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 aDataTable
with the Orders table from the Northwind database. ACommandBuilder
is used to generate the updating logic. The default view of theDataTable
is bound to a data grid on the form.- Update
Button.Click
Creates a new
Transaction
object on theConnection
of theSelectCommand
of theDataAdapter
. TheTransaction
is associated with theConnection
objects for the update commands generated for theDataAdapter
by theCommandBuilder
. TheUpdate( )
method of theDataAdapter
is called to updateDataTable
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 ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.