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;