- Here's the Scenario
- The Solution
- Using the SqlTransaction Object
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using the SqlTransaction Object
Using the SqlTransaction object is simple; there aren't many steps to add transactional support to your code. Here's a summary:
You need a SqlConnection object that has been opened with its Open() method.
Call the SqlConnection.BeginTransaction() method, which returns a SqlTransaction object.
Execute any number of SQL statements, with calls such as SqlCommand.ExecuteReader() or SqlCommand.ExecuteNonQuery().
When you decide whether everything was successful, call either the SqlTransaction.Commit() or the SqlTransaction.Abort() method.
There are some details that you need to know regarding the SqlConnection.BeginTransaction() method. Three overloads are available:
One takes no arguments and runs at an unspecified isolation level (which will be whatever isolation level was previously set).
The second overload takes a single argument that specifies the isolation level.
The third overload takes two arguments: the isolation level and a string with the name of the transaction. Table 1 shows the available isolation levels.
Table 1 Isolation Levels for Use in the SqlConnection.BeginTransaction() Method
|
Member Name |
Description |
|
Chaos |
The pending changes from more highly isolated transactions cannot be overwritten. |
|
ReadCommitted |
Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. |
|
ReadUncommitted |
A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored. |
|
RepeatableRead |
Locks are placed on all data used in a query, preventing other users from updating the data. Prevents non-repeatable reads, but phantom rows are still possible. |
|
Serializable |
A range lock is placed on the dataset, preventing other users from updating or inserting rows into the dataset until the transaction is complete. |
|
Unspecified |
A different isolation level than the one specified is being used, but the level cannot be determined. |
Simple Code Example
Listings 1–2 show very simple code examples that use the SqlTransaction object.
Listing 1—A Simple Use of the SqlTransaction Object (C#)
// Create the connection and transaction objects.
SqlConnection objConnection = new SqlConnection( strConnectString );
objConnection.Open();
SqlTransaction objTransaction = objConnection.BeginTransaction();
// Do some stuff here...
SqlCommand objCommand =
new SqlCommand( "Insert into Table (Name) VALUES ('Rick')",
objConnection );
objCommand.ExecuteNonQuery();
// Commit the transaction.
objTransaction.Commit();
// Close the connection.
objConnection.Close();
Listing 2—A Simple Use of the SqlTransaction Object (VB)
' Create the connection and transaction objects.
Dim objConnection As New SqlConnection( strConnectString )
objConnection.Open()
Dim objTransaction As SqlTransaction = objConnection.BeginTransaction()
' Do some stuff here...
Dim objCommand As _
new SqlCommand( "Insert into Table (Name) VALUES ('Rick')", objConnection )
objCommand.ExecuteNonQuery()
' Commit the transaction.
objTransaction.Commit()
' Close the connection.
objConnection.Close()
Let's take a look at the code in Listings 1–2. It starts off by creating a SqlConnection object. Then it gets a SqlTransaction object by calling the SqlConnection.BeginTransaction() method.
I threw in some SQL stuff to give you an example of what you might do in the course of the transaction. The SQL stuff creates a SqlCommand object with a SQL string that performs an insert. The SQL is executed when the SqlCommand.ExecuteNonQuery() method is called.
Finally, the transaction is committed with a call to the SqlTransaction.Commit() method. And the SqlConnection object is then closed with a call to its Close() method.
Robust Code Example
While the code in Listings 1–2 is simple, it's not very robust. That's because it doesn't catch exceptions or abort the transaction for any reason. Listings 3–4 show a more robust example that catches exceptions, and aborts the transaction if an exception is caught.
Listing 3—A More Robust Use of the SqlTransaction Object (C#)
// Create the connection and transaction objects.
SqlConnection objConnection = new SqlConnection( strConnectString );
SqlTransaction objTransaction = null;
try
{
objConnection.Open();
objTransaction = objConnection.BeginTransaction();
// Do some stuff here...
SqlCommand objCommand =
new SqlCommand( "Insert into Table (Name) VALUES ('Rick')",
objConnection );
objCommand.ExecuteNonQuery();
// Commit the transaction.
objTransaction.Commit();
}
catch
{
if( objTransaction != null )
{
objTransaction.Abort();
}
}
finally
{
// Close the connection.
if( objConnection.State == ConnectionState.Open )
objConnection.Close();
}
}
Listing 4—A More Robust Use of the SqlTransaction Object (VB)
' Create the connection and transaction objects.
Dim objConnection As New SqlConnection( strConnectString )
Dim objTransaction As SqlTransaction = Nothing
Try
objConnection.Open()
objTransaction = objConnection.BeginTransaction()
' Do some stuff here...
Dim objCommand As _
new SqlCommand( "Insert into Table (Name) VALUES ('Rick')",
objConnection )
objCommand.ExecuteNonQuery()
' Commit the transaction.
objTransaction.Commit()
Catch
If objTransaction ,> Nothing Then
objTransaction.Abort()
End If
Finally
' Close the connection.
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
End Try