Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: .NET3.5ASP.NETLINQ
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.
 
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
Phan7om430
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Found the culprit; Lazy loading. Solved it with calling .ToList() method before iteration over the query results
 
Thanks all for your time Smile | :)
  Permalink  
Comments
Sandeep Mewara at 23-Jun-12 9:30am
   
Good to know. :thumbsup:
Rate this: bad
good
Please Sign up or sign in to vote.

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[^]
  Permalink  
Comments
Phan7om at 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 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

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 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 "";
  Permalink  
Comments
Phan7om at 23-Jun-12 6:17am
   
and how does it solves my problem ?
Rate this: bad
good
Please Sign up or sign in to vote.

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..
  Permalink  
Comments
Phan7om at 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

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,518
1 OriginalGriff 478
2 Manas Bhardwaj 349
3 Tadit Dash 285
4 Damith Weerasinghe 260
0 Sergey Alexandrovich Kryukov 9,575
1 OriginalGriff 5,856
2 Peter Leow 4,405
3 Maciej Los 3,540
4 Abhinav S 3,513


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 23 Jun 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid