忽略TransactionScope中的SqlTransaction.Commit [英] Ignore SqlTransaction.Commit within TransactionScope

问题描述

我们正在逐渐取代实体框架（4.3.1）中的遗留数据访问代码。在某些情况下，我们无法避免在一个工作单位中使用两种数据访问方式。理想情况下，这应该在一次交易中完成。然而，旧代码使用 SqlTransaction s调用 Commit（），当工作完成后，EF管理它自己的交易。

所以我们想到在 TransactionScope 。但是，即使 TransactionScope 未完成，始终执行周围 TransactionScope 中的提交。这个代码片段说明了我的问题：

  using（var conn = new SqlConnection（connection string ））
 {
 conn.Open（）; 
使用（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（）; 
} 
 //实际上，上面的代码是遗留的DAL的一部分，是不可变的。 
 //（不能在tr.Commit之前插入SaveChanges）。 
 context.SaveChanges（）; 
 if（< all ok>）//用于异常处理的伪代码。 
 scope.Complete（）; 
} 
} 
  

更新语句仍然提交当 scope.Complete（）没有被击中。

所以看来，我不能使用 TransactionScope 强制旧的数据访问代码和上下文中的一个 SaveChanges 在一个事务中执行。有没有办法推翻SqlTransaction.Commit语句？

我知道在这里有关于TransactionScope和SqlTransaction的更多帖子，但是他们所有（正确地）说使用TransactionScope时，使用SqlTransaction是不必要的（也不推荐）。但是不使用SqlTransaction这里不是一个选项。我们有一个遗产框架，提交自己的 SqlTransaction ，并且没有api挂钩到它的事务机制。

解决方案

当scope.Complete（）未命中时，更新语句仍然提交。

哦不！ TransacationScope不被使用 ¹ 。

仅限自动注册 在之后打开（或 之前）TransactionScope。

将打开 TransactionScope 应该修复此问题（即使使用手动交易？），因为Connection将然后[通常]在环境TS上下文中自动登录。

可以将现有连接注册到环境事务范围内： connection.EnlistTransaction

或者，可以从现有事务创建TS，例如新的TransactionScope（交易），这可能或可能不是有用的。

如果完全正常，创建手动交易，但是TS（在陷阱被弄清楚！）使处理事务变得更加简单和容易。至少在大多数情况下：）

快乐编码！

---

¹ TS不用于更新语句。它仍然可以用于 context.SaveChanges（），因为这将打开一个新的连接，然后自动登录。 p>

我已经提供了一些上面的选项，虽然我不清楚这个简单的嵌套事务。看到上下文中使用的（密封的）API可能会揭示更多关于限制/限制的见解。

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.

解决方案

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.

这篇关于忽略TransactionScope中的SqlTransaction.Commit的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！