Checking for Concurrency Violations

Problem

You need to check for concurrency violations.

Solution

Use 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

The 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 constants using 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); // Set the timestamp to the new value in the data source and // call accept changes. conn.Open( ); e.Row["rowversion"] = (Byte[])cmd.ExecuteScalar( ); conn.Close( ); e.Row.AcceptChanges( ); } } private void Update(SqlDataAdapter da, DataTable dt, TextBox field1TextBox) { // Move the value for the field named Field1 // from the form to the DataTable for updating. dt.Rows[0]["Field1"] = field1TextBox.Text; // Update the row table. try { da.Update(dt); } catch (DBConcurrencyException ex) { // Error if timestamp does not match MessageBox.Show(ex.Message); dt.RejectChanges( ); } } private void DisplayRow(DataTable dt, int index) { if (dt.TableName == "A") { aIdTextBox.Text = dt.Rows[index]["Id"].ToString( ); aField1TextBox.Text = dt.Rows[index]["Field1"].ToString( ); aRowversionTextBox.Text = Convert.ToBase64String( (Byte[])dt.Rows[index]["rowversion"]); } else { bIdTextBox.Text = dt.Rows[index]["Id"].ToString( ); bField1TextBox.Text = dt.Rows[index]["Field1"].ToString( ); bRowversionTextBox.Text = Convert.ToBase64String( (Byte[])dt.Rows[index]["rowversion"]); } } private void updateButton_Click(object sender, System.EventArgs e) { if(((Button)sender).Tag.ToString( ) == "A") { Update(daA, dtA, aField1TextBox); DisplayRow(dtA, 0); } else { Update(daB, dtB, bField1TextBox); DisplayRow(dtB, 0); } } private void refreshButton_Click(object sender, System.EventArgs e) { if(((Button)sender).Tag.ToString( ) == "A") { daA.Fill(dtA); DisplayRow(dtA, 0); } else { daB.Fill(dtB); DisplayRow(dtB, 0); } }

Discussion

A timestamp is a data type that automatically generates an 8-byte binary value guaranteed to be unique within the database.

The T-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The timestamp data type defined by the SQL-92 standard is equivalent to the T-SQL datetime data type.

SQL Server 2000 introduced the data type synonym rowversion for the timestamp data type and recommends its use wherever possible in DDL statements. One difference between the timestamp data type and its data type synonym rowversion is that the timestamp data type does not require a column name while the rowversion data type does.

A table can have only one timestamp column. The value of the timestamp field is updated with the current database timestamp value from the @@DBTS function each time a row having a timestamp column is inserted or updated. This makes it a poor choice as part of a key, especially a primary key, since the index would be rebuilt with each row inserted or updated and the performance would suffer.

The timestamp value is not related to the date and time that data is inserted or updated. To record the time of record inserts and updates, define a datetime column in the table and create update and insert triggers to set its value.