Ignore SqlTransaction.Commit in TransactionScope

Question

We are in the process of gradually replacing the legacy data access code with the entity structure (4.3.1). In some cases, we cannot avoid using both methods of accessing data in one unit of work. Ideally, this should be done in one transaction. However, the old code uses SqlTransaction , which calls Commit() when a unit of work is executed, and EF manages its own transactions.

So, we thought about packing the "old" and "new" code in TransactionScope . However, Commit inside the TransactionScope environment is always executed, even if the TransactionScope not completed. This piece of code 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 persistent when scope.Complete() misses.

Thus, I cannot use TransactionScope to force the old data access code and SaveChanges out of context to execute in a single transaction. Or is there a way to cancel the SqlTransaction.Commit statement?

I know there are more posts about TransactionScope and SqlTransaction here, but they all (fairly) say that using SqlTransaction is not required (and not recommended) when using TransactionScope. But not using SqlTransaction is not an option here. We have an outdated structure that commits its own SqlTransaction and does not have an api to connect to the transaction mechanism.

Answer 1

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

Oh no! TransacationScope not used ¹ .

Autocompletion only works if the connection is open after (or inside) the TransactionScope.

Entering Open inside a TransactionScope should fix this problem (even with a manual transaction?), Because the connection will then [usually] automatically start in the context of the surrounding TS.

An existing connection can be credited to the external transaction area: connection.EnlistTransaction(Transaction.Current) .

Alternatively, TS can be created from an existing transaction, for example. new TransactionScope(transaction) , which may or may not be useful here.

Creating a manual transaction, if it’s fine, but TS (after getchas is calculated!) Simplifies and simplifies working with transactions .. at least in most cases :)

Happy coding!

---

¹ TS is not used for “update approval”. It will [probably] be used for context.SaveChanges() , as it will open a new connection, which will then be automatically credited.

I have provided some of the options above, although I'm not sure about a simple "nested" transaction. Seeing that the API (sealed?) Used in context can show more information about constraints / constraints.