Recipe 6.3 Nesting Manual Transactions with the SQL Server .NET Data Provider

Problem

You need to create a nested transaction using the SQL Server .NET data provider, but the Begin( ) command that you need is only available with the OLE DB .NET data provider. The SQL Server data provider appears to provide no built-in support for nested transactions. You want to nest transactions when using it.

Solution

Simulate nested transactions with savepoints when using the SQL Server .NET data provider, manage and control the lifetime of the SqlTransaction class, and create the required exception handling.

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. A savepoint is created if the first record insert succeeds. If the insert of the second record fails, the transaction is rolled back to the savepoint and the first record insert is committed; otherwise, both record inserts are committed.

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

Example 6-4. File: NestedManualTransactionForm.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 NestedTransactionForm_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( );
    }
    catch (Exception ex)
    {
        // Exception occurred. Roll back the transaction.
        tran.Rollback( );
        MessageBox.Show(ex.Message + Environment.NewLine + 
            "Transaction rollback (records 1 and 2).");
        conn.Close( );
        return;
    }

    tran.Save("SavePoint1");

    try
    {
        // Insert the records into the table.
        if (categoryName2TextBox.Text.Trim( ).Length == 0)
            // If CategoryName is empty, make it null (invalid).
            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 (records 1 and 2).");
    }
    catch (SqlException ex)
    {
        tran.Rollback("SavePoint1");
        tran.Commit( );
        MessageBox.Show(ex.Message + Environment.NewLine + 
            "Transaction commit (record 1)." + Environment.NewLine +
            "Transaction rollback (record 2).");
    }
    finally
    {
        conn.Close( );
    }

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

Discussion

The OLE DB .NET data provider's transaction class OleDbTransaction has a Begin( ) method that is used to initiate a nested transaction. A nested transaction allows part of a transaction to be rolled back without rolling back the entire transaction. An InvalidOperationException is raised if the OLE DB data source does not support nested transactions.

The SQL Server .NET data provider's transaction class SqlTransaction does not have a Begin( ) method to initiate a nested transaction. Instead, it has a Save( ) method that creates a savepoint in the transaction that can later be used to roll back a portion of the transaction—to the savepoint rather than rolling back to the start of the transaction. The savepoint is named using the only argument of the Save( ) method. An overload of the Rollback( ) method of the SqlTransaction class accepts an argument that you can use to specify the name of the savepoint to roll back to.