How to do transaction control on data access using DataAdapter and Stored Procedure in C#?

Question

How to do transaction control on data access using DataAdapter and Stored Procedure in C#? Currently I want to execute 2 stored procedure calls via DataAdapter, but I want to do transaction control on it. Is there any way to do it?

Answer 1

The preferred way of doing so is to use transaction scopes to handle this for you. Just surround the body of code that invoke both stored procedure calls with a new TransactionScope:

using(TransactionScope scope = new TransactionScope())
{
  // your ADO.NET code that calls sprocs ...
}

Any calls to the database that occur on the same connection will automatically be combined into a single transaction. You can also specify whether the code within the transaction scope should enlist in an existing transaction or start its own via the optional TransactionScopeOption parameter.

This is the preferred manner of combining calls into a single transaction. The alternative, is to manually acquire a DBTransaction by calling Connection.BeginTransaction() - performing your work and then calling tran.Commit().

Answer 2

I believe that the easiest way to do this would be to wrap both calls in a TransactionScope. See the example on this page:

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx