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;