Ignore SqlTransaction.Commit within TransactionScope

Question

We are in a process of gradually replacing legacy data access code by entity framework (4.3.1). In some occasions we can't avoid using both ways of data access in one unit of work. Ideally, this should be done in one transaction. However, the old code uses SqlTransactions that call Commit() when a unit of work is done and EF manages its own transactions.

So we thought of wrapping "old" and "new" code in a TransactionScope. However, a Commit within a surrounding TransactionScope is always executed, even if the TransactionScope is not completed. This code snippet illustrates my problem:

using (var conn = new SqlConnection("connection string"))
{
  conn.Open();
  using (var scope = new TransactionScope())
  {
    using (var tr = conn.BeginTransaction())
    {
      using (var cmd = conn.CreateCommand())
      {
        cmd.Transaction = tr;
        cmd.CommandText = "some update statement";
        cmd.ExecuteNonQuery();
      }
      tr.Commit();
    }
    // In reality the code above is part of a legacy DAL, immutable.
    // (can't insert SaveChanges before tr.Commit).
    context.SaveChanges();
    if (<all ok>) // pseudo code for exception handling.
        scope.Complete(); 
  }
}

The update statement is still committed when scope.Complete() is not hit.

So as it seems, I can not use TransactionScope to force the old data access code and a SaveChanges from a context to execute in one transaction. Or is there a way to overrule the SqlTransaction.Commit statement?

I know that there are more posts here about TransactionScope and SqlTransaction, but they all (rightly) say that using SqlTransaction is not necessary (nor recommended) when using TransactionScope. But not using SqlTransaction is not an option here. We have a legacy framework that commits its own SqlTransactions and that has no api to hook into its transaction mechanism.

@LadislavMrnka No, apart from the connection string in the config file. — Gert Arnold

Unknown Unknown · Accepted Answer · 2012-07-10T19:22:33

The update statement is still committed when scope.Complete() is not hit.

Oh no!! The TransacationScope Is Not Being Used¹.

Auto-enlist only works if the Connection is open after (or inside) the TransactionScope.

Putting the Open inside the TransactionScope should fix this issue (even with the manual transaction?) as the Connection will then [usually] auto-enlist in the ambient TS context.

An existing connection can be enlisted into the ambient transaction scope: connection.EnlistTransaction(Transaction.Current).

Alternatively, the TS can be created from an existing transaction, e.g. new TransactionScope(transaction), which may or may not be helpful here.

Creating a manual transaction if perfectly fine, but TS (after the gotchas are figured out!) makes dealing with transactions simpler and easier .. at least in most cases :)

Happy coding!

¹ The TS isn't being used for "the update statement". It will still [likely] be used for context.SaveChanges() as that will open a new connection which is then auto-enlisted.

I have provided some options above, although I am unsure about the plain "nested" transaction. Seeing the (sealed?) API used in context might reveal more insights as to limitations/restrictions.

Ignore SqlTransaction.Commit within TransactionScope

1 Answers