C# 我可以从 SqlConnection 对象获取对挂起事务的引用吗？

Can I get a reference to a pending transaction from a SqlConnection object?

提问by Rune

Suppose someone (other than me) writes the following code and compiles it into an assembly:

假设有人（除了我）编写了以下代码并将其编译为程序集：

using (SqlConnection conn = new SqlConnection(connString)) 
{
    conn.Open();
    using (var transaction = conn.BeginTransaction())
    {
        /* Update something in the database */
        /* Then call any registered OnUpdate handlers */
        InvokeOnUpdate(conn);

        transaction.Commit();
    }
}

The call to InvokeOnUpdate(IDbConnection conn) calls out to an event handler that I can implement and register. Thus, in this handler I will have a reference to the IDbConnection object, but I won't have a reference to the pending transaction. Is there any way in which I can get a hold of the transaction? In my OnUpdate handler I want to execute something similar to the following:

对 InvokeOnUpdate(IDbConnection conn) 的调用调出我可以实现和注册的事件处理程序。因此，在这个处理程序中，我将拥有对 IDbConnection 对象的引用，但我不会拥有对挂起事务的引用。有什么方法可以让我掌握交易吗？在我的 OnUpdate 处理程序中，我想执行类似于以下内容的内容：

private void MyOnUpdateHandler(IDbConnection conn) 
{
    var cmd = conn.CreateCommand();
    cmd.CommandText = someSQLString;
    cmd.CommandType = CommandType.Text;

    cmd.ExecuteNonQuery();
}

However, the call to cmd.ExecuteNonQuery() throws an InvalidOperationException complaining that

但是，对 cmd.ExecuteNonQuery() 的调用会引发 InvalidOperationException 抱怨

"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".

“当分配给命令的连接处于挂起的本地事务中时，ExecuteNonQuery 要求命令具有事务。命令的事务属性尚未初始化”。

Can I in any way enlist my SqlCommand cmd with the pending transaction? Can I retrieve a reference to the pending transaction from the IDbConnection object (I'd be happy to use reflection if necessary)?

我可以以任何方式将我的 SqlCommand cmd 用于挂起的事务吗？我可以从 IDbConnection 对象中检索对挂起事务的引用吗（如有必要，我很乐意使用反射）？

采纳答案by JoshBerke

Wow I didn't believe this at first. I am surprised that CreateCommand()doesn't give the command it's transaction when using local SQL Server transactions, and that the transaction is not exposed on the SqlConnectionobject. Actually when reflecting on SqlConnectionthe current transaction is not even stored in that object. In the edit bellow, I gave you some hints to track down the object via some of their internal classes.

哇，我一开始不相信这个。我很惊讶CreateCommand()在使用本地 SQL Server 事务时没有给出它的事务命令，并且事务没有在SqlConnection对象上公开。实际上在反映SqlConnection当前事务时甚至没有存储在该对象中。在下面的编辑中，我给了你一些通过它们的内部类追踪对象的提示。

I know you can't modify the method but could you use a TransactionScope around the method bar? So if you have:

我知道您不能修改该方法，但是您可以在方法栏周围使用 TransactionScope 吗？所以如果你有：

public static void CallingFooBar()
{
   using (var ts=new TransactionScope())
   {
      var foo=new Foo();
      foo.Bar();
      ts.Complete();
   }
}

This will work, I tested using similar code to yours and once I add the wrapper all works fine if you can do this of course. As pointed out watch out if more then one connection is opened up within the TransactionScopeyou'll be escalated to a Distributed Transaction which unless your system is configured for them you will get an error.

这会起作用，我使用与您类似的代码进行了测试，一旦我添加了包装器，一切都可以正常工作，当然，如果您可以这样做的话。正如所指出的，如果在其中打开了多个连接，TransactionScope您将被升级为分布式事务，除非您的系统为它们配置，否则您将收到错误消息。

Enlisting with the DTC is also several times slower then a local transaction.

使用 DTC 登记也比本地事务慢几倍。

Edit

编辑

if you really want to try and use reflection, SqlConnection has a SqlInternalConnection this in turn has a Property of AvailableInternalTransaction which returns an SqlInternalTransaction, this has a property of Parent which returns the SqlTransaction you'd need.

如果你真的想尝试使用反射，SqlConnection 有一个 SqlInternalConnection，这反过来有一个可用的属性，它返回一个 SqlInternalTransaction，它有一个 Parent 的属性，它返回你需要的 SqlTransaction。

回答by lvaneenoo

The command object can only be assigned a transaction object using one of its constructors. You can go for the .NET 2.0 approach and use a TransactionScope object which is defined in the System.Transactionsnamespace (has a dedicated assembly).

命令对象只能使用其构造函数之一分配事务对象。您可以采用 .NET 2.0 方法并使用在System.Transactions命名空间中定义的 TransactionScope 对象（具有专用程序集）。

   using System.Transactions;

    class Foo
    {   
        void Bar()
        {
            using (TransactionScope scope = new TransactionScope())
            {
                // Data access
                // ...
                scope.Complete()
            }
        }
    }

The System.Transactions approach uses in conjunction with SQL Server 2005 a lightweight transaction coordinator (LTM). Be careful not to use multiple connection objects in your transaction scope or the transaction will get promoted as it is seen as distributed. This more resource-intensive version of the transaction will then be handled by DTC.

System.Transactions 方法结合 SQL Server 2005 使用轻量级事务协调器 (LTM)。注意不要在您的事务范围内使用多个连接对象，否则事务将被提升，因为它被视为分布式。这种资源密集型事务版本随后将由 DTC 处理。

回答by Denis

I am a big proponent of simple so how about writing a wrapper over IDBConnection (DELEGATE PATTERN) that exposes Transaction. (Sorry for VB.NET code, I am writing this in VB.NET right now) Something like this:

我是 simple 的大力支持者，那么如何在 IDBConnection（DELEGATE PATTERN）上编写一个公开事务的包装器。（对不起，VB.NET 代码，我现在正在 VB.NET 中写这个）像这样：

  Public class MyConnection
      Implements IDbConnection

      Private itsConnection as IDbConnection
      Private itsTransaction as IDbTransaction

      Public Sub New(ByVal conn as IDbConnection)
         itsConnection = conn
      End Sub

      //...  'All the implementations would look like
      Public Sub Dispose() Implements IDbConnection.Dispose
         itsConnection.Dispose()
      End Sub
      //...

      //     'Except BeginTransaction which would look like
       Public Overridable Function BeginTransaction() As IDbTransaction Implements IDbConnection.BeginTransaction
         itsTransaction = itsConnection.BeginTransaction()
         Return itsTransaction
       End Function  


      // 'Now you can create a property and use it everywhere without any hacks
       Public ReadOnly Property Transaction
          Get
              return itsTransaction
          End Get
       End Property

    End Class

So you would instantiate this as:

因此，您可以将其实例化为：

Dim myConn as new MyConnection(new SqlConnection(...))

and then you can get the transaction any time by using:

然后您可以随时使用以下方法获取交易：

 myConn.Transaction

回答by victorvartan

For anybody who is interested in the C# version of the decorator class that Denis made in VB.NET, here it is:

对于任何对 Denis 在 VB.NET 中制作的装饰器类的 C# 版本感兴趣的人，这里是：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DataAccessLayer
{
    /// <summary>
    /// Decorator for the connection class, exposing additional info like it's transaction.
    /// </summary>
    public class ConnectionWithExtraInfo : IDbConnection
    {
        private IDbConnection connection = null;
        private IDbTransaction transaction = null;

        public IDbConnection Connection
        {
            get { return connection; }
        }

        public IDbTransaction Transaction
        {
            get { return transaction; }
        }

        public ConnectionWithExtraInfo(IDbConnection connection)
        {
            this.connection = connection;
        }

        #region IDbConnection Members

        public IDbTransaction BeginTransaction(IsolationLevel il)
        {
            transaction = connection.BeginTransaction(il);
            return transaction;
        }

        public IDbTransaction BeginTransaction()
        {
            transaction = connection.BeginTransaction();
            return transaction;
        }

        public void ChangeDatabase(string databaseName)
        {
            connection.ChangeDatabase(databaseName);
        }

        public void Close()
        {
            connection.Close();
        }

        public string ConnectionString
        {
            get 
            {
                return connection.ConnectionString; 
            }
            set 
            {
                connection.ConnectionString = value;
            }
        }

        public int ConnectionTimeout
        {
            get { return connection.ConnectionTimeout; }
        }

        public IDbCommand CreateCommand()
        {
            return connection.CreateCommand();
        }

        public string Database
        {
            get { return connection.Database; }
        }

        public void Open()
        {
            connection.Open();
        }

        public ConnectionState State
        {
            get { return connection.State; }
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            connection.Dispose();
        }

        #endregion
    }
}

回答by Alvaro Rodriguez

In case anyone is interested in the reflection code to accomplish this, here it goes:

如果有人对实现此目的的反射代码感兴趣，请看这里：

    private static readonly PropertyInfo ConnectionInfo = typeof(SqlConnection).GetProperty("InnerConnection", BindingFlags.NonPublic | BindingFlags.Instance);
    private static SqlTransaction GetTransaction(IDbConnection conn) {
        var internalConn = ConnectionInfo.GetValue(conn, null);
        var currentTransactionProperty = internalConn.GetType().GetProperty("CurrentTransaction", BindingFlags.NonPublic | BindingFlags.Instance);
        var currentTransaction = currentTransactionProperty.GetValue(internalConn, null);
        var realTransactionProperty = currentTransaction.GetType().GetProperty("Parent", BindingFlags.NonPublic | BindingFlags.Instance);
        var realTransaction = realTransactionProperty.GetValue(currentTransaction, null);
        return (SqlTransaction) realTransaction;
    }

Notes:

笔记：

• The types are internal and the properties private so you can't use dynamic
• internal types also prevent you from declaring the intermediate types as I did with the first ConnectionInfo. Gotta use GetType on the objects

• 类型是内部的，属性是私有的，所以你不能使用动态
• 内部类型还阻止您像我在第一个 ConnectionInfo 中所做的那样声明中间类型。必须在对象上使用 GetType

回答by A77

In case where anyone faced this problem on .Net 4.5 you can use Transaction.Currentin System.Transactions.

如果有人在 .Net 4.5 上遇到这个问题，你可以Transaction.Current在System.Transactions.