I found a good article that summarizes the use of transaction very nicely. Quick and fast to get started. Covers using transactions on a dataset and on commands (kind of the same thing).

A snippet from ADO.Net Micorsoft Press. You can view the original text here http://www.microsoft.com/mspress/books/sampchap/5354g.asp

Submitting Updates in Transactions

What if you want to submit all of your updates as a single unit of work so that either all of the updates succeed or none of them does? The simple answer is to wrap your updates in a transaction. However, the DataAdapter does not expose a Transaction property.

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.

The following code snippet shows one way to accomplish this task:

Visual Basic .NET

Dim strConn, strSQL As StringstrConn = \"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\"Dim tbl As New DataTable()Dim cn As New OleDbConnection(strConn)Dim da As 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.Dim txn As OleDbTransaction = cn.BeginTransaction()'Set the Transaction property of the DataAdapter's Commands.da.UpdateCommand.Transaction = txnda.InsertCommand.Transaction = txnda.DeleteCommand.Transaction = txn
	
'Submit the changes.da.Update(tbl)
	
'Commit the changes and close the connection.txn.Commit()cn.Close()

Visual C# .NET

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.

If you use code that looks like the following, ADO.NET will throw an exception when you try to submit the pending changes:

Visual Basic .NET

Dim strConn, strSQL As StringDim tbl As New DataTable()Dim cn As New OleDbConnection(strConn)Dim da As New OleDbDataAdapter(strSQL, cn)Dim cb As New OleDbCommandBuilder(da)cn.Open()da.Fill(tbl)Dim txn As OleDbTransaction = cn.BeginTransaction()da.Update(tbl)txn.Commit()cn.Close()

Visual C# .NET

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();da.Fill(tbl);OleDbTransaction txn = cn.BeginTransaction();da.Update(tbl);txn.Commit();cn.Close();

When you call DataAdapter.Update, the CommandBuilder will fetch the required metadata from the database using the DataAdapter object's SelectCommand. We 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.

If we add the following line of code just before the call to the DataAdapter object's Update method, our code will succeed:

da.SelectCommand.Transaction = txn

However, this means that the CommandBuilder fetches schema information from your database within a transaction. Generally speaking, you want to touch as little data as possible in your database during a transaction. A more palatable option is to force the CommandBuilder to generate updating logic before starting the transaction. We can accomplish this by calling the CommandBuilder object's GetUpdateCommand (or GetInsertCommand or GetDeleteCommand) method.

We can then associate the Command objects that the CommandBuilder generated with our new Transaction object using the following code, and the DataAdapter will submit updates within the transaction:

Visual Basic .NET

Dim strConn, strSQL As StringDim tbl As New DataTable()Dim cn As New OleDbConnection(strConn)Dim da As New OleDbDataAdapter(strSQL, cn)Dim cb As New OleDbCommandBuilder(da)cn.Open()cb.GetUpdateCommand()da.Fill(tbl)Dim txn As OleDbTransaction = cn.BeginTransaction()cb.GetUpdateCommand.Transaction = txncb.GetInsertCommand.Transaction = txncb.GetDeleteCommand.Transaction = txnda.Update(tbl)txn.Commit()cn.Close()

Visual C# .NET

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

Using the TableMappings Collection

In Chapter 5, you learned how the DataAdapter object's TableMappings collection affects how the DataAdapter populates a DataSet via the Fill method. In the following code, calling the Fill method of the DataAdapter creates a new DataTable whose TableName property is set to Table:

Visual Basic .NET

Dim strConn, strSQL As StringstrConn = \"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\"Dim da As New OleDbDataAdapter(strSQL, strConn)Dim ds As New DataSet()da.Fill(ds)

Visual C# .NET

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\";OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strC onn);DataSet ds = new DataSet();da.Fill(ds);

If we want our new DataTable to have a TableName of Order Details, we can change our code in one of two ways. The first option is to use the overloaded Fill method to supply the desired TableName:

Visual Basic .NET

Dim da As New OleDbDataAdapter(strSQL, strConn)Dim ds As New DataSet()da.Fill(ds, \"Order Details\")

Visual C# .NET

OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);DataSet ds = new DataSet();da.Fill(ds, \"Order Details\");

The other option is to add an entry to the DataAdapter object's TableMappings collection so that the DataAdapter knows that it's associated with the Order Details DataTable:

Visual Basic .NET

Dim da As New OleDbDataAdapter(strSQL, strConn)da.TableMappings.Add(\"Table\", \"Order Details\")Dim ds As New DataSet()da.Fill(ds)

Visual C# .NET

OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);da.TableMappings.Add(\"Table\", \"Order Details\");DataSet ds = new DataSet();da.Fill(ds);

The TableMappings collection has a similar effect when you submit updates. If you supply just a DataSet object in the DataAdapter object's Update method, the DataAdapter will rely on its TableMappings collection to determine which DataTable in the DataSet to examine:

Visual Basic .NET

Dim da As New OleDbDataAdapter(strSQL, strConn)da.TableMappings.Add(\"Table\", \"Order Details\")'Define updating logic.Dim ds As New DataSet()da.Fill(ds)'Modify a series of rows.da.Update(ds)

Visual C# .NET

OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strC onn);//Define updating logic.da.TableMappings.Add(\"Table\", \"Order Details\");DataSet ds = new DataSet();da.Fill(ds);//Modify a series of rows.da.Update(ds);

If you have not populated the DataAdapter object's TableMappings collection, you must either use the Update method that accepts a DataSet and a table name or use the Update method that accepts a DataTable object:

Visual Basic .NET

Dim da As New OleDbDataAdapter(strSQL, strConn)'Define updating logic.Dim ds As New DataSet()da.Fill(ds, \"Order Details\")'Modify a series of rows.da.Update(ds, \"Order Details\")
	
'or
	
Dim da As New OleDbDataAdapter(strSQL, strConn)'Define updating logic.Dim tbl As New DataTable()da.Fill(tbl)'Modify a series of rows.da.Update(tbl)

Visual C# .NET

OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strC onn);//Define updating logic.DataSet ds = new DataSet();da.Fill(ds, \"Order Details\");//Modify a series of rows.da.Update(ds, \"Order Details\");
	
//or
	
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strC onn);//Define updating logic.DataTable tbl = new DataTable();da.Fill(tbl);//Modify a series of rows.da.Update(tbl);

As a basic rule, you should use the same logic to control the DataTable you're referencing in both DataAdapter.Fill and DataAdapter.Update.

The Best Way to Update

ADO.NET gives you many options for submitting changes. You can generate updating logic at run time using CommandBuilder objects. You can supply your own updating logic in code, submitting changes via INSERT, UPDATE, or DELETE queries or via stored procedure calls. You can also use the Data Adapter Configuration Wizard to generate such code easily at design time. Which of these options is right for you?

The answer really depends on the parameters of your application. You could get the best performance by configuring your DataAdapter objects to submit updates via stored procedure calls. However, if your application must work with databases, such as Microsoft Access, that don't support stored procedures, that solution is not appropriate. You'd be better off using INSERT, UPDATE, and DELETE queries. You'll need to consider such factors when deciding what's appropriate for your application.

From a general standpoint, I strongly recommend submitting changes via stored procedures whenever possible. If the ability to work with multiple back ends is a greater priority, use query-based updates (INSERT, UPDATE, DELETE) instead. Regardless of which option you choose, generate your own updating logic. Use the Data Adapter Configuration Wizard or a similar code-generation tool to save development time, but avoid generating updating logic at run time whenever possible. If you remember only one thing from this chapter, remember this: Don't rely on CommandBuilder objects in your applications unless absolutely necessary.

There are a number of more advanced updating scenarios that we've yet to discuss. How do I fetch newly generated autoincrement values? How do I submit changes from a DataSet that contains new and deleted rows to multiple related tables? How do I detect and handle failed update attempts? How can I use ADO.NET with distributed transactions? We'll cover these and other more advanced updating scenarios in the next chapter.