Implementing SQL Server transactions with ADO.NET

ADO.NET provides everything you need to work with back-end data, which can be as simple as reading a set of data from one table to performing a transaction. A transaction allows you to group database operations to ensure that all the operations are performed because, if one operation fails, the whole transaction fails. Let's take a closer look at using transactions within the .NET Framework.

Transaction overview

A transaction is a group of operations combined into a logical unit of work. Developers use transactions to control and maintain the consistency and integrity of each action in a transaction, despite errors that might occur in the system.

This is an all-or-nothing approach: either all operations in the transaction are performed, or none. This approach is necessary in real-time applications.

Banking is the standard example. A transaction would encompass the transfer of money from one account to another. The transfer is a transaction because the debit of money from one account and credit to another must be performed as a unit--one part cannot fail. Before delving into coding ADO.NET transactions, let's take a quick look at handling transactions in SQL.

SQL transactions

SQL allows a developer to work with transactions using two simple statements:

• Begin Transaction
• Commit Transaction

Everything that's between these two statements makes up the transaction. The Begin Transaction command begins the transaction, so every command that follows doesn't execute until issuing the Commit Transaction command. The ADO.NET approach is just as simple.

ADO.NET transactions

A transaction requires a database connection and a transaction object. The crux of utilizing transactions with SQL Server and ADO.NET is the SqlTransaction class. This class name varies depending upon the database platform utilized. For instance, the transaction class for OLEDB databases is OleDbTransaction.

The System.Data.SqlClient namespace contains the SqlTransaction class. There are two properties in the class:

• Connection: Indicates the SqlConnection object associated with the transaction.
• IsolationLevel: Specifies the transaction's IsolationLevel.

The IsolationLevel property is an Enumeration object with the following members:

• Chaos: The pending changes from more highly isolated transactions cannot be overwritten.
• ReadCommitted: Shared locks are held while the data is read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable 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 that is used in a query, preventing other users from updating the data. This prevents nonrepeatable reads, though 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.

The IsolationLevel allows a level of record locking, but this concept is beyond the scope of this article. The SqlTransaction object provides methods as well. You may use the following methods to work with the transaction:

• Commit: The database transaction is committed.
• Rollback: A database transaction is rolled back from a pending state. A transaction may not be rolled back once it's committed.
• Save: Creates a savepoint in the transaction that can be used to roll back a portion of the transaction, and specifies the savepoint name.

The following C# example puts all the pieces together:

using System;
using System.Data;
using System.Data.SqlClient;
namespace BuilderExamples {
class BuilderTransactionExample {
static void Main(string[] args) {
SqlTransaction trans = null;
SqlConnection conn = null;
SqlCommand comm = null;
try {
conn = new SqlConnection("server=(local);Initial
Catalog=Northwind;UID=ctester;PWD=password");
conn.Open();
comm = conn.CreateCommand();
trans = conn.BeginTransaction("BuilderTransaction");
comm.Connection = conn;
comm.Transaction = trans;
comm.CommandText = "Insert into Territories (TerritoryID, TerritoryDescription,
RegionID) VALUES (99001, 'Louisville', 4)";
comm.ExecuteNonQuery();
comm.CommandText = "Insert into Territories (TerritoryID, TerritoryDescription,
RegionID) VALUES (99002, 'Lexington', 4)";
comm.ExecuteNonQuery();
trans.Commit();
} catch(Exception e) {
try {
if (trans != null) {
trans.Rollback("BuilderTransaction");
}  }  catch (SqlException ex) {
if (trans.Connection != null)  {
Console.WriteLine("Exception" + ex.GetType() + " encountered while rolling back
transaction.");
}  }
Console.WriteLine("Exception " + e.GetType() + " encountered while inserting
data.");
} finally {
if (conn != null) {
conn.Close();
}  }  }  }  }

This simple console application attempts to insert two rows into a table in the Northwind database by following these steps:

1. Call the BeginTransaction method of the Connection object to mark the start of the transaction. The BeginTransaction method returns a reference to the Transaction. This reference is assigned to the Command objects that are enlisted in the transaction.
2. Assign the Transaction object to the Transaction property of the Command to be executed. If a Command is executed on a Connection with an active Transaction, and the Transaction object hasn't been assigned to the Transaction property of the Command, an exception is thrown.
3. Execute the required commands.
4. Call the Commit method of the Transaction object to complete the transaction, or call the Rollback method to cancel the transaction.

The equivalent VB.NET code is similar:

Imports System.Data
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim trans As SqlTransaction
Dim conn As SqlConnection
Dim comm As SqlCommand
Try
conn = New SqlConnection("server=(local);Initial
 Catalog=Northwind;UID=ctester;PWD=password")
conn.Open()
comm = conn.CreateCommand()
trans = conn.BeginTransaction("BuilderTransaction")
comm.Connection = conn
comm.Transaction = trans
comm.CommandText = "Insert into Territories (TerritoryID, TerritoryDescription,
 RegionID) VALUES (99003, 'Louisville', 4)"
comm.ExecuteNonQuery()
comm.CommandText = "Insert into Territories (TerritoryID, TerritoryDescription,
 RegionID) VALUES (99004, 'Lexington', 4)"
comm.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
Try
If Not (trans Is Nothing) Then
trans.Rollback("BuilderTransaction")
End If
Catch ex As SqlException
Console.WriteLine("Exception" + ex.GetType().ToString() + " encountered while rolling back transaction.")
End Try
Console.WriteLine("Exception " + e.GetType().ToString() + " encountered while inserting data.")
Finally
If Not (conn Is Nothing) Then
conn.Close()
End If
End Try
End Sub
End Module

Transaction complete

Though this is a simple example, it demonstrates how easy it is to utilize transactions in your .NET application. Remember, transactions are only necessary when the grouped commands are related.