Setting the Transaction Isolation Level

Each SQL Server connection (SQL session) can have its transaction isolation level set. The setting you assign remains until the connection is closed or until you assign a new setting. One way to assign the transaction isolation level is to add the SQL statement to your stored procedure. For example, to set the transaction isolation level to Repeatable Read, add the following SQL statement to your stored procedure.

SQL

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Another way to set the transaction isolation level is to add a query hint to your SQL statement. For example, the following SQL statement overrides the current session's isolation setting and uses the Read Uncommitted isolation level to perform the query.

SQL

SELECT * FROM CUSTOMERS WITH (NOLOCK)

The transaction isolation level can also be set on the DbTransaction class, which the SqlTransaction class inherits from. Simply pass the desired transaction isolation level to the BeginTransaction method, as shown in the following code.

Visual Basic

Private Sub Button2_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles Button3.Click    Dim cnSetting As ConnectionStringSettings = _       ConfigurationManager.ConnectionStrings("NorthwindString")    Using cn As New SqlConnection()       cn.ConnectionString = cnSetting.ConnectionString       cn.Open()       Using tran As SqlTransaction = _          cn.BeginTransaction(System.Data.IsolationLevel.Serializable)          Try             'work code here             Using cmd As SqlCommand = cn.CreateCommand()                cmd.Transaction = tran                cmd.CommandText = "SELECT count(*) FROM employees"                Dim count As Integer = CInt(cmd.ExecuteScalar())                MessageBox.Show(count.ToString())             End Using             'if we made it this far, commit             tran.Commit()          Catch xcp As Exception             tran.Rollback()             'cleanup code             MessageBox.Show(xcp.Message)          End Try       End Using    End Using End Sub

private void button2_Click(object sender, EventArgs e) {    ConnectionStringSettings cnSetting =       ConfigurationManager.ConnectionStrings["NorthwindString"];    using (SqlConnection cn = new SqlConnection())    {       cn.ConnectionString = cnSetting.ConnectionString;       cn.Open();       using (SqlTransaction tran =          cn.BeginTransaction(System.Data.IsolationLevel.Serializable))       {          try          {             //work code here             using (SqlCommand cmd = cn.CreateCommand())             {                cmd.Transaction = tran;                cmd.CommandText = "SELECT count(*) FROM employees";                int count = (int)cmd.ExecuteScalar();                MessageBox.Show(count.ToString());             }             //if we made it this far, commit             tran.Commit();          }          catch (Exception xcp)          {             tran.Rollback();             //cleanup code             MessageBox.Show(xcp.Message);          }       }    } }

Table of content

Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics

Linux Application Development (2nd Edition)

ISBN: 735621411
EAN: 2147483647

Year: 2004
Pages: 85

Authors: Michael K. Johnson, Erik W. Troan

BUY ON AMAZON

Building Web Applications with UML (2nd Edition)

Systematic Software Testing (Artech House Computer Library)

Microsoft Windows Server 2003(c) TCP/IP Protocols and Services (c) Technical Reference

Cisco CallManager Fundamentals (2nd Edition)

Data Structures and Algorithms in Java

Google Maps Hacks: Tips & Tools for Geographic Searching and Remixing