ADO.NET 3.5 Cookbook, 2nd Edition

7.9. Checking for Concurrency Violations

Problem

You need to check for concurrency violations while using optimistic concurrency.

Solution

Use a timestamp data type column to manage data concurrency violations.

The solution uses a single table named ConcurrencyViolation in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE ConcurrencyViolation(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Version timestamp NOT NULL)

Execute the following T-SQL batch to create sample data needed by the solution:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO ConcurrencyViolation VALUES (1, 'Field1.1', null);
	INSERT INTO ConcurrencyViolation VALUES (2, 'Field1.2', null);
	INSERT INTO ConcurrencyViolation VALUES (3, 'Field1.3', null);

The solution creates a DataTable named TableA and fills it with the schema and data from the ConcurrencyViolation table in the AdoDotNet35Cookbook database.

The timestamp column is made read-only. An event handler named da_RowUpdated is created for the RowUpdated event of the DataAdapter. The event handler da_RowUpdated checks that an error did not occur (Status=Continue) and that a row was either inserted or updated. For those rows, the current value of the timestamp column is retrieved from the ConcurrencyViolaton in the database and used to update the row in the DataTable TableA. The parameterized update command is created for the DataAdapter—the WHERE clause of the update command matches both ...