我可以从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".

Can我以任何方式使我的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，而这又有一个AvailableInternalTransaction的属性，该属性返回SqlInternalTransaction，这有Parent的属性，该属性返回SqlTransaction。 'd需要。

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屋！