Checking for Concurrency Violations

[ Team LiB ] Recipe 6.10 Checking for Concurrency Violations Problem You need to check for concurrency violations. Solution Use a timestamp column to manage data concurrency violations.
Nội dung trích xuất từ tài liệu:
Checking for Concurrency Violations [ Team LiB ]Recipe 6.10 Checking for Concurrency ViolationsProblemYou need to check for concurrency violations.SolutionUse a timestamp column to manage data concurrency violations.The schema of table TBL0610 used in this solution is shown in Table 6-12. Table 6-12. TBL0610 schema Column name Data type Length Allow nulls?Id int 4 NoField1 nvarchar 50 Yesrowversion timestamp 8 NoThe sample code contains four event handlers and two methods:Form.Load Sets up the sample by creating two DataTable objects: A and B. For each, a DataAdapter is created and a handler is attached to handle the RowUpdated event. The DataAdapter is used to fill the DataTable with both schema and data from table TBL0610 in the database. The update command is defined for the DataAdapter using the timestamp column in the WHERE clause to catch concurrency errors. The first row from both tables is displayed on the form simulating the view of two different simultaneous data users.DataAdapter.RowUpdated Checks if the row was inserted or updated. For those rows, the current value of the timestamp column is retrieved from the TBL0610 in the database and used to update the row in the DataTable.Update( ) This method copies the specified value for Field1 from the text box into the DataTable. The DataAdapter is used to update DataTable changes to table TBL0610 in the database. If a DBConcurrencyException is encountered, the change to the row is rejected.DisplayRow( ) This method is used to display data from a specified row in DataTable A or B to the corresponding user display on the form.Update Button.Click Calls the Update( ) method to update changes made to the row in either DataTable A or B. The DisplayRow( ) method is called to update the corresponding row displayed on the form.Refresh Button.Click Gets the latest values for the row in DataTable A or B from table TBL0610 in the database. The DisplayRow( ) method is called to update the corresponding row displayed on the form.The C# code is shown in Example 6-28.Example 6-28. File: RowversionForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Windows.Forms;using System.Text;using System.Data;using System.Data.SqlClient;private DataTable dtA, dtB;private SqlDataAdapter daA, daB;private const String TABLENAME = TBL0610;// . . .private void RowversionForm_Load(object sender, System.EventArgs e){// Build statements to select only the row for ID = 1// and to update the data for the DataAdapter.String selectText = SELECT Id, Field1, rowversion FROM + TABLENAME + WHERE Id = 1;String updateText = UPDATE + TABLENAME + + SET Field1 = @Field1 + WHERE Id = 1 AND rowversion = @rowversion;// Create table A and fill it with the schema.dtA = new DataTable(A);daA = new SqlDataAdapter(selectText, ConfigurationSettings.AppSettings[Sql_ConnectString]);daA.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);daA.FillSchema(dtA, SchemaType.Source);dtA.Columns[rowversion].ReadOnly = false;daA.Fill(dtA);// Create the update command and define the parameters.daA.UpdateCommand = new SqlCommand(updateText, daA.SelectCommand.Connection);daA.UpdateCommand.CommandType = CommandType.Text;daA.UpdateCommand.Parameters.Add(@Id, SqlDbType.Int, 0, Id);daA.UpdateCommand.Parameters[@Id].SourceVersion = DataRowVersion.Original;daA.UpdateCommand.Parameters.Add(@Field1, SqlDbType.NVarChar, 50, Field1);daA.UpdateCommand.Parameters[@Field1].SourceVersion = DataRowVersion.Current;daA.UpdateCommand.Parameters.Add(@rowversion, SqlDbType.Timestamp, 0, rowversion);daA.UpdateCommand.Parameters[@rowversion].SourceVersion = DataRowVersion.Original;// Create table B and fill it with the schema.dtB = new DataTable(B);daB = new SqlDataAdapter(selectText, ConfigurationSettings.AppSettings[Sql_ConnectString]);daB.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);daB.FillSchema(dtB, SchemaType.Source);dtB.Columns[rowversion].ReadOnly = false;daB.Fill(dtB);// Create the update command and define the parameters. daB.UpdateCommand = new SqlCommand(updateText, daB.SelectCommand.Connection); daB.UpdateCommand.CommandType = CommandType.Text; daB.UpdateCommand.Parameters.Add(@Id, SqlDbType.Int, 0, Id); daB.UpdateCommand.Parameters[@Id].SourceVersion = DataRowVersion.Original; daB.UpdateCommand.Parameters.Add(@Field1, SqlDbType.NVarChar, 50, Field1); daB.UpdateCommand.Parameters[@Field1].SourceVersion = DataRowVersion.Current; daB.UpdateCommand.Parameters.Add(@rowversion, SqlDbType.Timestamp, 0, rowversion); daB.UpdateCommand.Parameters[@rowversion].SourceVersion = DataRowVersion.Original; // Display the first row (ID=1) from both tables on the form. DisplayRow(dtA, 0); DisplayRow(dtB, 0);}private void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e){ // Check if an insert or update operation is being performed. if(e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update)) { // Build a command object to retrieve the updated timestamp. String sqlGetRowVersion = SELECT rowversion FROM + TABLENAME + WHERE Id = + e.Row[Id]; SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings[Sql_ConnectString]); SqlCommand cmd = new SqlCommand(sqlGetRowVersion, conn); ...