Performing a Transaction 

Transactions in ADO.NET are used when you want to bind multiple tasks together so that they execute as a single unit of work. For example, imagine that an application performs two tasks. First, it updates a table with order information. Second, it updates a table containing inventory information, debiting the items ordered. If either task fails, the both updates are rolled back.

Determining the Transaction Type

A transaction considered to be a local transaction when it is a single-phase transaction and is handled by the database directly. Transactions are considered to be distributed transactions when they are coordinated by a transaction monitor and use fail-safe mechanims (such as two-phase commit) for transaction resolution.

Each of the .NET Framework data providers has its own Transaction object for performing local transactions. If you require a transaction to be performed in a SQL Server database, then choose a System.Data.SqlClient transaction. For an Oracle transaction, use the System.Data.OracleClient provider. In addition, there is a new DbTransaction class that is available for writing provider-independent code that requires transactions.

Note

Transactions are most efficient when performed on the server. If you are working with a SQL Server database that makes extensive use of explicit transactions, you should consider writing them as stored procedures using the Transact-SQL BEGIN TRANSACTION statement. For more information on performing server-side transactions, see SQL Server Books Online.

Performing a Transaction Using a Single Connection

In ADO.NET, you control transactions with the Connection object. You can initiate a local transaction with the BeginTransaction method. Once you have begun a transaction, you can enlist a command in that transaction with the Transaction property of a Command object. You can then commit or roll back modifications made at the data source based on the success or failure of the components of the transaction.

Note

The EnlistDistributedTransaction method should not be used for a local transaction.

The scope of the transaction is limited to the connection. The following example performs an explicit transaction consisting of two separate commands in the try block. The commands execute INSERT statements against the Production.ScrapReason table in the AdventureWorks SQL Server 2005 sample database, which are committed if no exceptions are thrown. The code in the catch block rolls back the transaction if an exception is thrown. If the transaction is aborted or the connection is closed before the transaction has completed, it is automatically rolled back.

The following steps are used to perform a transaction:

Procedures

To perform a transaction

1. Call the BeginTransaction method of the SqlConnection object to mark the start of the transaction. The BeginTransaction method returns a reference to the transaction. This reference is assigned to the SqlCommand objects that are enlisted in the transaction.

2. Assign the Transaction object to the Transaction property of the SqlCommand to be executed. If a command is executed on a connection with an active transaction, and the Transaction object has not been assigned to the Transaction property of the Command object, an exception is thrown.

3. Execute the required commands.

4. Call the Commit method of the SqlTransaction object to complete the transaction, or call the Rollback method to abort the transaction. If the connection is closed or disposed before either the Commit or Rollback methods have been executed, the transaction is rolled back.

The following code example demonstrates transactional logic using ADO.NET with Microsoft SQL Server.

Using connection As SqlConnection = New SqlConnection(connectString)
    connection.Open()

    ' Start a local transaction.
    Dim sqlTran As SqlTransaction = connection.BeginTransaction()

    ' Enlist the command in the current transaction.
    Dim command As SqlCommand = connection.CreateCommand()
    command.Transaction = sqlTran

    Try
        command.CommandText = _
          "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong size')"
        command.ExecuteNonQuery()
        command.CommandText = _
          "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong color')"
        command.ExecuteNonQuery()
        sqlTran.Commit()
        Console.WriteLine("Both records were written to database.")
    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Console.WriteLine("Neither record was written to database.")
        sqlTran.Rollback()
    End Try
End Using

using (SqlConnection connection = new SqlConnection(connectString))
{
    connection.Open();

    // Start a local transaction.
    SqlTransaction sqlTran = connection.BeginTransaction();

    // Enlist the command in the current transaction.
    SqlCommand command = connection.CreateCommand();
    command.Transaction = sqlTran;

    try
    {
        command.CommandText =
          "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong size')";
        command.ExecuteNonQuery();
        command.CommandText =
          "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong color')";
        command.ExecuteNonQuery();
        sqlTran.Commit();
        Console.WriteLine("Both records were written to database.");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        Console.WriteLine("Neither record was written to database.");
        sqlTran.Rollback();
    }
}

See Also

Concepts

Performing a Distributed Transaction
Transaction Fundamentals

Other Resources

Performing Transactions