ADO.NET in a Nutshell

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 ...