O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required