How to convert to ADO.NET transactions rather than SQL Server Transactions?

Question

Right now i have code that initiates transactions on SQL Server using the intended method:

ExecuteNonQuery(connection, "BEGIN TRANSACTION");
try
{
   DoABunchOnStuff(connection);
   DoSomeMoreStuff(connection);
   JustAFewMoreThings(connection);

   ExecuteNonQuery(connection, "COMMIT TRANSACTION");
} 
catch (Exception)
{  
   ExecuteNonQuery(connection, "ROLLBACK TRANSACTION");
   throw;
}

Now i'm looking at thinking about the possibility of investigating the idea of using the transaction abstraction provided by ADO.NET:

DbTransaction trans = connection.BeginTransaction();
try
{
   DoABunchOnStuff(connection);
   DoSomeMoreStuff(connection);
   JustAFewMoreThings(connection);

   trans.Commit();
} 
catch (Exception)
{  
   trans.Rollback();
   throw;
}

Problem with this simple conversion from SQL Server based transactions, to ADO.NET transactions, is the error:

ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

Am i correct in assuming that if i wanted to use ADO.NET transactions i would have to completely gut the infrastructure, passing along a DbTransaction object to every method that does, or may, operate inside a transaction?

Answer 1

Am i correct in assuming that if i wanted to use ADO.NET transactions i would have to completely gut the infrastructure, passing along a DbTransaction object to every method that does, or may, operate inside a transaction?

Yes, exactly - you basically need to associate the transaction you've created with each SqlCommand that ought to be executed under that transaction's umbrella - so you'd have to have something like:

DbTransaction trans = connection.BeginTransaction();
try
{
   DoABunchOnStuff(connection, trans);
   DoSomeMoreStuff(connection, trans);
   JustAFewMoreThings(connection, trans);

   trans.Commit();
} 
catch (Exception)
{  
   trans.Rollback();
   throw;
}

and inside those methods something along the lines of:

public void DoABunchOnStuff(SqlConnection connection, SqlTransaction trans)
{
    using(SqlCommand cmd = new SqlCommand(--sql stmt--, connection, trans)
    {
       ........
    } 
} 

Answer 2

You are correct, but since you are evidently keeping the connection open the whole time, you could replace this with a TransactionScope instead; it won't promote to DTC as long as there's only one open connection.

Example:

using (TransactionScope tsc = new TransactionScope())
{
    DoABunchOnStuff(connection);
    DoSomeMoreStuff(connection);
    JustAFewMoreThings(connection);
    tsc.Complete();
}

Notes about using the TransactionScope:

• You must make sure to include Transaction Binding = Explicit Unbind in your connection string. By default transactions are run in implicit-unbind mode which means that they switch to auto-commit mode if the transaction times out. You almost never want the default behaviour, as it can interfere with the atomicity of your transactions and cause what some people refer to as data corruption (even though it's not actual "corruption"). As long as you use the correct parameters in your connection string, you don't need to worry about this.

• TransactionScope will promote to DTC (distributed transaction) if there is more than one connection in scope, which includes linked servers and OPENROWSET. Although this might seem like undesirable behaviour, your code isn't going to be transactionally safe any other way. Executing manual BEGIN TRAN statements on multiple connections and putting multiple ROLLBACK statements in an exception handler does not ensure atomicity of the entire transaction.

• Transaction Scopes are designed to be nested and will automatically figure out the difference between beginning a new transaction and enlisting in an existing one. This is a lot more powerful than matching up BEGIN TRAN and COMMIT/ROLLBACK statements, as the latter rely on a connection-local transaction count, whereas the former is actually... scoped. Using TransactionScope is similar to structured transaction handling in SQL Server using SAVE TRAN, TRY/CATCH, and named ROLLBACK - you do not need to worry about what happens if a downstream process or procedure flubs the transactional logic, which is a serious risk when sending raw BEGIN and ROLLBACK statements over ADO.NET.

Answer 3

You may also want to take a look at Linq to SQL. As you can also "SubmitChanges()" (or not submit them) in code to the database. This means you can wrap it in a try catch just like your transaction. This is a bit of an infratstucture change as well but with SQLMetal you can auto generate all the necessary classes. It may or may not be right for your situation.

more info: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx