ADO.NET 3.5 Cookbook, 2nd Edition

7.11. Using Transaction Isolation Levels to Protect Data

Problem

You want to effectively use transaction isolation levels to ensure data consistency for a range of data rows.

Solution

Use the Begin() method of the Transaction object to control isolation level.

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

	USE AdoDotNet35Cookbook 
	GO
	CREATE TABLE Transaction IsolationLevel(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

Create the test data the solution needs by executing the following T-SQL batch:

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

The solution uses snapshot isolation. Enable snapshot isolation in the AdoDotNet35Cookbook database by executing the following T-SQL statement:

	ALTER DATABASE AdoDotNet35Cookbook SET ALLOW_SNAPSHOT_ISOLATION ON

The solution outputs the contents of the TransactionIsolationLevel table to the console. Next, a Connection is opened and a transaction started with the isolation level SnapShot. An attempt is made to insert two records into the TransactionIsolationLevel table within the transaction. The TransactionIsolationLevel table is read into a DataTable and output to the console between the two insert commands showing ...