ADO.NET Cookbook

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 ...