ADO.NET Cookbook

6.12. 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

Set and use isolation levels as shown in the following example.

The sample code contains three event handlers:

Start Tran Button.Click

Opens a Connection and starts a transaction with the specified isolation level: Chaos, ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, or Unspecified. Within the transaction, a DataTable is filled with the Orders table from the Northwind database. The default view of the table is bound to the data grid on the form.

Cancel Button.Click

Rolls back the transaction, closes the connection, and clears the data grid.

Form.Closing

Rolls back the transaction and closes the connection.

The C# code is shown in Example 6-30.

Example 6-30. File: TransactionIsolationLevelsForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; private SqlConnection conn; private SqlTransaction tran; // . . . private void startButton_Click(object sender, System.EventArgs e) { startButton.Enabled = false; // Get the user-defined isolation level. IsolationLevel il = IsolationLevel.Unspecified; if(chaosRadioButton.Checked) il = IsolationLevel.Chaos; else if(readCommittedRadioButton.Checked) il = IsolationLevel.ReadCommitted; else if(readUncommittedRadioButton.Checked) il = IsolationLevel.ReadUncommitted; ...