Recipe 6.5 Using a Transaction with a DataAdapter

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;