Recipe 6.5 Using a Transaction with a DataAdapter

Problem

You need to use a transaction when updating a data source using a DataAdapter.

Solution

Associate a Transaction with the appropriate Command object from the DataAdapter.

The sample code contains three event handlers:

Form.Load

Sets up the sample by using a DataAdapter to load a DataTable with the Orders table from the Northwind database. A CommandBuilder is used to generate the updating logic. The default view of the DataTable is bound to a data grid on the form.

Update Button.Click

Creates a new Transaction object on the Connection of the SelectCommand of the DataAdapter. The Transaction is associated with the Connection objects for the update commands generated for the DataAdapter by the CommandBuilder. The Update( ) method of the DataAdapter is called to update DataTable changes to the Orders table. If no errors are encountered, the transaction is committed; otherwise, all changes made are rolled back.

Refresh Button.Click

Clears and reloads the Orders DataTable.

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

Example 6-7. File: TransactionDataAdapter.cs

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

private const String ORDERS_TABLE = "Orders";

private DataTable dt;
private SqlDataAdapter da;
private SqlCommandBuilder cb;

//  . . . 

private void TransactionDataAdapterForm_Load(object sender,
    System.EventArgs e)
{
    String sqlText = "SELECT * FROM Orders";

    // Fill the Orders table for editing.
    da = new SqlDataAdapter(sqlText,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    // Stop updating when an error is encountered for roll back.
    da.ContinueUpdateOnError = false;
    // Create CommandBuilder and generate updating logic.
    cb = new SqlCommandBuilder(da);
    cb.GetDeleteCommand( );
    cb.GetInsertCommand( );
    cb.GetUpdateCommand( );
    // Create table and fill with orders schema and data.
    dt = new DataTable(ORDERS_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 updateButton_Click(object sender, System.EventArgs e)
{
    // Create and open the connection.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    conn.Open( );

    // Create and start the transaction.
    SqlTransaction tran = null;
    tran = conn.BeginTransaction( );

    // Associate CommandBuilder generated update commands
    // with the transaction.
    da.SelectCommand.Transaction = tran;

    // Update the data source.
    try
    {
        // Submit the changes.
        da.Update(dt);

        // Success. Commit.
        tran.Commit( );
    }
    catch (Exception ex)
    {
        // Exception. Roll back.
        tran.Rollback( );

        MessageBox.Show(ex.Message + Environment.NewLine +
            "Transaction rolled back.");
    }
    finally
    {
        conn.Close( );
    }
}

private void refreshButton_Click(object sender, System.EventArgs e)
{
    // Refresh the orders data.
    dt.Clear( );
    da.Fill(dt);
}

Discussion

You can use a transaction with a DataAdapter to allow the roll back of updates made by the DataAdapter in the event of an error.

If, as in the solution, a CommandBuilder is used to generate the update logic for the DataAdapter, associate the Transaction with the SelectCommand of the DataAdapter as shown in the solution code:

da.SelectCommand.Transaction = tran;

If custom update logic is used for the DataAdapter, the Transaction must be associated with the DeleteCommand, InsertCommand, and UpdateCommand of the DataAdapter, but not the SelectCommand, as shown in the following code:

da.DeleteCommand.Transaction = tran;
da.InsertCommand.Transaction = tran;
da.UpdateCommand.Transaction = tran;