InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

Question

4.90/5 (3 votes)

See more:

.NET3.5

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.

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.

C#

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 "";
}

DB server is Sql Server 2005
Thanks

Posted 21-Jun-12 19:26pm

Phan7om

Add a Solution

4 solutions

Solution 4

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:

C#

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

Posted 23-Jun-12 0:12am

kishhr

Comments

Phan7om 23-Jun-12 6:17am

and how does it solves my problem ?

Solution 3

Somewhere used used sqldatareader n its not yet yet been closed.. You given an inappropriate code. just search SqlDataReader in your code then close it before executing any command..

Posted 22-Jun-12 23:38pm

allagi.arun

Comments

Phan7om 23-Jun-12 6:17am

just for your info I've searched in the whole solutions and found only single instance of datareader which is closed. why can't people see LINQ tag, I'm using LINQ

Solution 1

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[^]

Posted 21-Jun-12 20:35pm

Sandeep Mewara

Comments

Phan7om 22-Jun-12 3:01am

Hi Mewara,

I've checked this link before. But I'm not using DataReader anywhere in the whole project and only LINQ is used.

thanks for your time :)

Sandeep Mewara 22-Jun-12 3:23am

That's what I said, its not clear from the code above but based on error it is being used internally. Look for how 'ABCDataContext' internally works.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Phan7om · Accepted Answer · 2012-06-22T23:15:00

Solution 2

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

Thanks all for your time :)

Posted 22-Jun-12 23:15pm

Phan7om

Comments

Sandeep Mewara 23-Jun-12 9:30am

Good to know. :thumbsup: