InvalidOperationException:已经有与此命令关联的打开的DataReader，必须首先关闭它. [英] InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

问题描述

大家好

我在生产环境中遇到上述异常.我已经搜索了例外，大多数情况下的解决方案是使用MARS.但是在连接字符串中添加了MARS之后，出现了以下异常:

Hi All

I am getting the above exception in production environment. I''ve googled the exception, and the solution in most cases is to use MARS. But after adding the MARS in the connection string, I got the following exception:

System.Data.SqlClient.SqlException: The transaction operation cannot be performed because there are pending requests working on this transaction.

但是目前我不是在寻找解决方案，而是希望你们中的任何一个都可以帮助我复制这个问题.无论在内部还是在UAT上，都无法在任何环境中复制此信息.突然冒出来.直到昨天，它运行良好.

当我提交事务时，会抛出以上异常.

But currently I''m not looking for the solution, rather if any of you can help me replicate this issue. This is not getting replicated in any environment neither in house, nor at UAT. came suddenly out of nowhere. Until yesterday it was running perfectly.

The above exceptions are thrown when I commit the transaction.

public string Subscribe(DateTime date)
{
    ABCDataContext dataContext = new ABCDataContext();
    return Process(RAKBankDataAccess.GetRegistration(dataContext, date), dataContext);
}

private string Process(IQueryable<DeliveryRegistration> registrations,
                       ABCDataContext dataContext)
{
        System.Data.Common.DbTransaction trans = null;
        if (dataContext.Connection.State != System.Data.ConnectionState.Open)
            dataContext.Connection.Open();
        trans = dataContext.Connection.BeginTransaction();
        dataContext.Transaction = trans;
        dataContext.CommandTimeout = 0;
        foreach (DeliveryRegistration dr in registrations)
        {
            //perform some modifications
            //Add some new rows

            transactionCount++;
           if (transactionCount == 250)
            {
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
                if (dataContext.Connection.State!=
                    System.Data.ConnectionState.Open)
                    dataContext.Connection.Open();
                trans = dataContext.Connection.BeginTransaction();
                dataContext.Transaction = trans;

                transactionCount = 1;
            }

        }

        dataContext.SubmitChanges();
        dataContext.Transaction.Commit();

        return "";
}

数据库服务器是Sql Server 2005
谢谢

DB server is Sql Server 2005
Thanks

推荐答案

找到了罪魁祸首；延迟加载.在查询结果迭代之前，通过调用.ToList()方法解决了该问题.

谢谢您的宝贵时间:)

Found the culprit; Lazy loading. Solved it with calling .ToList() method before iteration over the query results

Thanks all for your time :)

与DataReader相关的代码不是直接可见的，但看起来像是在数据访问层中使用的.在尝试在当前方案中重用之前，您的数据读取器仍会以某种方式使用.如果不查看确切的代码，就很难就今天而不是以前的原因共享观点.您可以尝试使用数据读取器打开和关闭所需的适当代码来处理它.

有关此内容的详细信息，请参见: MSDN博客:已经有与此命令相关联的打开的DataReader，必须首先将其关闭". ="http://blogs.msdn.com/b/spike/archive/2009/08/20/there-is-already-an-open-datareader-associated-with-this-command-which-must-be- close-first-explained.aspx"target =" _ blank"title =" New Window> ^ ]

Code related to DataReader is not visible directly, but looks like being used in Data access layer. Somehow your datareader is still in use before you try to re-use in your current scenario. Sharing views on why today and not before would be difficult without seeing the exact code. You can try to handle it using proper code needed for datareader opening and closure.

Have a full detail on the same here: MSDN Blog: "There is already an open DataReader associated with this Command which must be closed first" explained[^]

在下面的代码中，问题是:

公共字符串Subscribe(DateTime日期)
{
ABCDataContext dataContext =新的ABCDataContext();
返回Process(RAKBankDataAccess.GetRegistration(dataContext，date)，dataContext);
}

私有字符串Process(IQueryable< deliveryregistration>注册，
ABCDataContext dataContext)
{
System.Data.Common.DbTransaction trans = null;
如果(dataContext.Connection.State！= System.Data.ConnectionState.Open)
dataContext.Connection.Open();
trans = dataContext.Connection.BeginTransaction();
dataContext.Transaction = trans;
dataContext.CommandTimeout = 0;
foreach(注册中的DeliveryRegistration博士)
{
//进行一些修改
//添加一些新行

transactionCount ++;
如果(transactionCount == 250)
{
dataContext.SubmitChanges();
dataContext.Transaction.Commit();
trans = dataContext.Connection.BeginTransaction();
dataContext.Transaction = trans;

transactionCount = 1;
}

}
if(transactionCount％250！= 0)
zz
返回"；
}

使用以下代码:

In the below code the problem is:

public string Subscribe(DateTime date)
{
ABCDataContext dataContext = new ABCDataContext();
return Process(RAKBankDataAccess.GetRegistration(dataContext, date), dataContext);
}

private string Process(IQueryable<deliveryregistration> registrations,
ABCDataContext dataContext)
{
System.Data.Common.DbTransaction trans = null;
if (dataContext.Connection.State != System.Data.ConnectionState.Open)
dataContext.Connection.Open();
trans = dataContext.Connection.BeginTransaction();
dataContext.Transaction = trans;
dataContext.CommandTimeout = 0;
foreach (DeliveryRegistration dr in registrations)
{
//perform some modifications
//Add some new rows

transactionCount++;
if (transactionCount == 250)
{
dataContext.SubmitChanges();
dataContext.Transaction.Commit();
trans = dataContext.Connection.BeginTransaction();
dataContext.Transaction = trans;

transactionCount = 1;
}

}
if(transactionCount % 250 != 0)
zz
return "";
}

Use the below code:

foreach (DeliveryRegistration dr in registrations)
       {
           //perform some modifications
           //Add some new rows

           transactionCount++;
          if (transactionCount == 250)
           {
               dataContext.SubmitChanges();
               dataContext.Transaction.Commit();
               if (dataContext.Connection.State!=
                   System.Data.ConnectionState.Open)
                   dataContext.Connection.Open();
               trans = dataContext.Connection.BeginTransaction();
               dataContext.Transaction = trans;

               transactionCount = 1;
           }

       }
       if(transactionCount % 250 != 0)
{
       dataContext.SubmitChanges();
       dataContext.Trans%action.Commit();
}

       return "";

这篇关于InvalidOperationException:已经有与此命令关联的打开的DataReader，必须首先关闭它.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！