Hi Rex,

You can have update/delete/insert all run under the same transaction. The DataAdapter does not actually submit the updates. It simply hands the work off to the Command objects in its UpdateCommand, InsertCommand, and DeleteCommand properties. The Command object exposes a Transaction property, so in order to submit the changes using the DataAdapter, you must set the Transaction property of the Command objects that the DataAdapter will use.

Here is some sample code:

string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
          "Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +
         "FROM [Order Details] WHERE OrderID = 10503 " +
         "ORDER BY ProductID";
DataTable tbl = new DataTable();
OleDbConnection cn = new OleDbConnection(strConn);
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
//Define updating logic for the DataAdapter.

//Open the connection and fetch the results of the query.
cn.Open();
da.Fill(tbl);

//Modify the contents of the DataTable.

//Create a new transaction.
OleDbTransaction txn = cn.BeginTransaction();
//Set the Transaction property of the DataAdapter's Commands.
da.UpdateCommand.Transaction = txn;
da.InsertCommand.Transaction = txn;
da.DeleteCommand.Transaction = txn;

//Submit the changes.
da.Update(tbl);

//Commit the changes and close the connection.
txn.Commit();
cn.Close();

It’s slightly more challenging to submit changes in a transaction if you’re relying on the CommandBuilder object to generate your updating logic. The CommandBuilder does not actually generate the updating logic when you instantiate it. If you instantiate a CommandBuilder object and later call DataAdapter.Update, the CommandBuilder will not actually build the updating logic until you call the DataAdapter object’s Update method. This behavior poses a slight problem if you want to use the CommandBuilder to submit changes in a transaction.

When you call DataAdapter.Update, the CommandBuilder will fetch the required metadata from the database using the DataAdapter object’s SelectCommand. You have not associated the Command object in the SelectCommand property with the newly created transaction. As a result, the CommandBuilder cannot use the SelectCommand and the CommandBuilder throws an exception.

Force the CommandBuilder to generate updating logic before starting the transaction. You can accomplish this by calling the CommandBuilder object’s GetUpdateCommand (or GetInsertCommand or GetDeleteCommand) method. You can then associate the Command objects that the CommandBuilder generated with the new Transaction object using the following code, and the DataAdapter will submit updates within the transaction:

string strConn, strSQL;
...
DataTable tbl = new DataTable();
OleDbConnection cn = new OleDbConnection(strConn);
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
cn.Open();
cb.GetUpdateCommand();
da.Fill(tbl);
OleDbTransaction txn = cn.BeginTransaction();
cb.GetUpdateCommand().Transaction = txn;
cb.GetInsertCommand().Transaction = txn;
cb.GetDeleteCommand().Transaction = txn;
da.Update(tbl);
txn.Commit();
cn.Close();

>All,
>
>I want to use the .Update method of a DataAdapter with transactions. Do I need to set a separate transaction for the Insert, Update, and Delete command objects, or is there a way to set a single transaction to handle the whole DataAdapter?
>
>Thanks
>
>Rex