我可以从 SqlConnection 对象获取对挂起事务的引用吗? [英] Can I get a reference to a pending transaction from a SqlConnection object?

问题描述

假设有人(除了我)编写了以下代码并将其编译为程序集:

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();
    }
}

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

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:

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

    cmd.ExecuteNonQuery();
}

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

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

"ExecuteNonQuery 需要命令进行交易时分配给命令的连接是在待处理的本地事务中.这命令的事务属性尚未初始化".

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

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

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)?

推荐答案

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

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 SqlConnection object. Actually when reflecting on SqlConnection the 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.

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

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

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

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

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 TransactionScope you'll be escalated to a Distributed Transaction which unless your system is configured for them you will get an error.

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

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

如果您真的想尝试使用反射，SqlConnection 有一个 SqlInternalConnection，这又具有一个可返回的 SqlInternalTransaction 属性，它有一个 Parent 属性，它返回您需要的 SqlTransaction.

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 对象获取对挂起事务的引用吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！