Recipe 6.2 Using Manual Transactions

Problem

You need to explicitly begin, control, and end a transaction within a .NET application.

Solution

Use the Connection object with structured exceptions (try . . . catch . . . finally).

The sample code contains two event handlers:

Form.Load

Sets up the sample by filling a DataTable with the Categories table from the Northwind sample database. The default view of the table is bound to a data grid on the form.

Insert Button.Click

Inserts user-entered data for two Categories records into the Northwind database within a manual transaction. If either record insert fails, both inserts are rolled back; otherwise, both record inserts are committed.

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

Example 6-3. File: ManualTransactionForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private const String CATEGORIES_TABLE = "Categories";

private DataTable dt;
private SqlDataAdapter da;

//  . . . 

private void ManualTransactionForm_Load(object sender, System.EventArgs e)
{
    // Fill the categories table.
    String sqlText = "SELECT CategoryID, CategoryName, " +
        "Description FROM Categories";
    da = new SqlDataAdapter(sqlText,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    dt = new DataTable(CATEGORIES_TABLE);
    da.FillSchema(dt, SchemaType.Source);
    da.Fill(dt);

    // Bind the default view of the table to the grid.
    dataGrid.DataSource = dt.DefaultView;        
}

private void insertButton_Click(object sender, System.EventArgs e)
{
    String sqlText = "INSERT " + CATEGORIES_TABLE + " "+ 
        "(CategoryName, Description) VALUES " +
        "(@CategoryName, @Description)";

    // Create the connection.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    // Create the transaction.
    conn.Open( );
    SqlTransaction tran = conn.BeginTransaction( );

    // Create command in the transaction with parameters.
    SqlCommand cmd = new SqlCommand(sqlText, conn, tran);
    cmd.Parameters.Add(new SqlParameter("@CategoryName",
        SqlDbType.NVarChar, 15));
    cmd.Parameters.Add(new SqlParameter("@Description",
        SqlDbType.NVarChar, 100));

    try
    {
        // Insert the records into the table.
        if (categoryName1TextBox.Text.Trim( ).Length == 0)
            // If CategoryName is empty, make it null (invalid).
            cmd.Parameters["@CategoryName"].Value = DBNull.Value;
        else
            cmd.Parameters["@CategoryName"].Value =
                categoryName1TextBox.Text;
        cmd.Parameters["@Description"].Value =
                description1TextBox.Text;
        cmd.ExecuteNonQuery( );

        if (categoryName2TextBox.Text.Trim( ).Length == 0)
            cmd.Parameters["@CategoryName"].Value = DBNull.Value;
        else
            cmd.Parameters["@CategoryName"].Value =
                categoryName2TextBox.Text;
        cmd.Parameters["@Description"].Value =
            description2TextBox.Text;
        cmd.ExecuteNonQuery( );

        // If okay to here, commit the transaction.
        tran.Commit( );

        MessageBox.Show("Transaction committed.");
    }
    catch (Exception ex)
    {
        // Exception occurred. Roll back the transaction.
        tran.Rollback( );
        MessageBox.Show(ex.Message + Environment.NewLine + 
            "Transaction rollback.");
    }
    finally
    {
        conn.Close( );
    }

    // Refresh the data.
    da.Fill(dt);
}

Discussion

Manual transactions allow control over the transaction boundary through explicit commands to start and end the transaction. There is no built-in support for distributed transactions spanning multiple resources with manual transactions.

.NET data providers make available objects to enable manual transactions. The Connection object has a BeginTransaction( ) method that is used to start a transaction. If successful, the method returns a Transaction object that is used to perform all subsequent actions associated with the transaction, such as committing or aborting. Calling the BeginTransaction( ) method does not implicitly cause all subsequent commands to execute within the transaction. The Transaction property of the Command object must be set to a transaction that has already been started for the command to execute within the transaction.

Once started, the transaction remains in a pending state until it is explicitly committed or rolled back using the Commit( ) or Rollback( ) methods of the Transaction object. The Commit( ) method of the Transaction is used to commit the database transaction. The Rollback( ) method of the Transaction is used to roll back a database transaction from a pending state. An InvalidOperationException will be raised if Rollback( ) is called after Commit( ) has been called.

The isolation level of the transaction can be specified through an overload of the BeginTransaction( ) method and if it is not specified, the default isolation level ReadCommitted is used.

Unlike automatic transactions, manual transactions must be explicitly committed or rolled back using the Commit( ) or Rollback( ) method. If possible, use the .NET data provider transaction management exclusively; avoid using other transaction models, such as the one provided by SQL Server. If this is necessary for any reason, Recipe 6.3 discusses using the SQL Server transaction model together with the .NET SQL Server data provider transaction management.

The IDbTransaction interface is implemented by .NET data providers that access relational databases. Applications create an instance of the class implementing the IDbTransaction interface rather than creating an instance of the interface directly. Classes that inherit IDbTransaction must implement the inherited members and typically define provider-specific functionality by adding additional members.

The SQL .NET data provider allows savepoints to be defined allowing a transaction to be partially rolled back to a point in the transaction other than its beginning. The OLE DB .NET data provider allows nested transactions to be started within the parent transaction; the parent transaction cannot commit until all its nested transactions have committed.