ADO.NET 3.5 Cookbook, 2nd Edition

7.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 Command object for the DataAdapter.

The solution uses a table named DataAdapterTransaction in the AdoDotNet35Cookbook database. Execute the T-SQL in Example 7-8 to create the table.

Example 7-8. Create table DataAdapterTransaction

USE AdoDotNet35Cookbook
GO
CREATE TABLE DataAdapterTransaction (
    Id int NOT NULL PRIMARY KEY,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL )

The solution creates a DataAdapter that loads a DataTable with the DataAdapterTransaction table in the AdoDotNet35Cookbook database. The method InsertRecords() adds records to a DataTable and updates the DataAdapterTransaction table using a transaction to rollback the update if all records cannot be added. A CommandBuilder is used to generate the updating logic. A Transaction object on the Connection of the SelectCommand of the DataAdapter is created. 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. The InsertRecords() method is called twice. The first call successfully adds two records. The second call fails to add two records because the Id of the second record ...