16.5 Transactions Using a DataAdapter

The DataAdapter uses its Command objects DeleteCommand , InsertCommand , and UpdateCommand to update changes back to the data source. As a result, using transactions from a DataAdapter isn't very different from using them with the Command object directly. If custom updating logic is being used with the DataAdapter , simply create the transaction and assign it to the three update Command objects for the DataAdapter . The following example illustrates how to use transactions with the DataSet and the DataAdapter objects with custom update logic:

 String connString = "Data Source=(local);Integrated security=SSPI;" +      "Initial Catalog=Northwind;"; String sqlSelect = "SELECT * FROM Orders"; SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlDataAdapter da = new SqlDataAdapter(sqlSelect, conn); DataSet ds = new DataSet(); // define update logic for the data adapter // load  data from the data source into the DataSet da.Fill(ds, "Orders"); // start the transaction SqlTransaction tran = conn.BeginTransaction(); // associate transaction with the data adapter command objects da.DeleteCommand.Transaction = tran; da.InsertCommand.Transaction = tran; da.UpdateCommand.Transaction = tran; // ... modify the data in the DataSet // submit changes, commit or rollback, and close the connection try {     da.Update(ds, "Orders");          // commit if successful     tran.Commit(); } catch (Exception) {     tran.Rollback(); } finally {     conn.Close(); } 

When the CommandBuilder generates the updating logic used by the DataAdapter , it doesn't generate updating logic when it is instantiated . Good design dictates minimizing data interaction within a transaction. This means that the updating logic for the CommandBuilder should be generated before the transaction is started, rather inside the transaction. This is accomplished by calling the GetDeleteCommand() , GetInsertCommand() , and GetUpdateCommand() methods of the CommandBuilder object prior to using it with a transaction the first time. The following example illustrates how to use a transaction with a DataAdapter object that uses a CommandBuilder object to provide update logic:

 // ... create the connection and data adapter as with custom update logic // use a command builder to define updating logic SqlCommandBuilder cb = new SqlCommandBuilder(da); // generate updating logic for command objects cb.GetDeleteCommand(); cb.GetInsertCommand(); cb.GetUpdateCommand(); // load  data from the data source into the DataSet da.Fill(ds, "Orders"); // start the transaction SqlTransaction tran = conn.BeginTransaction(); // associate transaction with command builder command objects cb.GetDeleteCommand().Transaction = tran; cb.GetInsertCommand().Transaction = tran; cb.GetUpdateCommand().Transaction = tran; // ... modify the data in the DataSet // submit changes, commit or rollback, and close the connection try {     da.Update(ds, "Orders");          // commit if successful     tran.Commit(); } catch (Exception) {     tran.Rollback(); } finally {     conn.Close(); }