How can I force TransactionScope to use the same connection across Database calls?

Question

Good Morning All,

I have code similar to the following

try{
    using(var tx = new TransactionScope()){

        var Xupdated  = someDao.DoSomeUpdateQuery(); //this dao uses MS Data ApplicationBlock

        var Yupdated = someDao.DoSomeOtherUpdateQuery(); //this dao also uses MS Data ApplicationBlock

     if(Xupdated && Yupdated)
     {
        tx.Complete();
     }

    }
} catch(Exception ex){

   DoSomethingWithTheException();
}

The dao methods have code like this

 try{
 var db = DatabaseFactory.CreateDatabase();
 var cmd = db.GetStoredProcCommand(someSP);
  var retVal = db.ExecuteNonQuery(cmd);
 return (retVal > 0);
} catch (SqlException ex){
    CustomException custom = new CustomException(ex.Message, ex);
  throw custom;

}

The problem here is when 'Yupdated' returns false, I want 'DoSomeUpdateQuery()' to be rolled back. Unfortunately, 'DoSomeUpdateQuery()' changes are committed. How can i remedy this? I put a breakpoint in and tx.Complete() is never called. Does anyone know how I can get the appropriate desired behavior here? Thanks in advance for any pointers.

Cheers,
~ck in San Diego

Answer 1

You already have the appropriate behavior as long as the DatabaseFactory class, whatever it is, behaves in a cooperating manner. If the someDao calls seem to act independently, it could mean they are actively creating an inner scope with TranscationScopeOption.Supress. Another issue could be if the CreateDatabase call internally caches some SqlConnection and does not relies on SqlClient connection pool, thus possibly by-passing the connection enrollment into the transaction.

As a side note, even when this works as desired, it will be mighty inefficient because you'll enroll two distinct connections into a distributed-transaction and this will slow processing a great deal. A good data access layer accepts the Sqlconnection to use as a parameter so it can share it between calls, thus keeping the transaction scope local on the server.