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();
}