locked
There is already an open DataReader associated with this Command which must be closed first RRS feed

  • Question

  • User379720387 posted

    I have this unexplained error coming from my WebService.asmx.

    20180915.01:02:21 UTC : QBWebConnector.SOAPWebService.do_sendRequestXML() : QBWC1041: SendRequestXML failed.

    In the SendRequestXML method a call is made to buildRequest() which checks if any data needs to be send. These are all EF queries.

    public ArrayList buildRequest()
            {
                var dbContext = new VTEntities1();
    
                //owners that need to be added
                var addOwners = dbContext.Owners.Where(o => o.EnteredByProviderId == theprovider && o.AddOwner == true /*&& o.ListId == null*/);
    
                //owners that need to be verified
                var verifyOwners = dbContext.Owners.Where(o => o.EnteredByProviderId == theprovider && o.VerifyCustomer == true /*&& o.ListId != null*/);
    
                //owners that need to be updated
                var updateOwners = dbContext.Owners.Where(o => o.EnteredByProviderId == theprovider && o.UpdateCustomer == true /*&& o.ListId != null*/);
    
                //ItemsTxns to be added
                var addTxns = dbContext.Transactions.Where(t => t.ProviderId == theprovider && t.AddTransaction == true/* && t.ListItemId == null*/);
    
                //Txns need verification
                var verifyTxns = dbContext.Transactions.Where(t => t.ProviderId == theprovider && t.VerifyTransaction == true);
    
                int i = 0;
    
                foreach (var item in addOwners)
                {
                    //do CustomerAdd
                    var custAdd = new XmlRqCreate().CustomerAddRq(item, i.ToString());
    
                    i++;
                    req.Add(custAdd);
                }
    etc.

    is the above code a potential candidate for generating these "open DataReader" issues?

    By the way this only happens in the live environment, locally there is no such error message.

    Error message: Server was unable to process request. ---> An error occurred while executing the command definition. See the inner exception for details. ---> There is already an open DataReader associated with this Command which must be closed first.

    More info:

    StackTrace =    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

       at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

       at QBWebConnector.localhost.WCWebServiceDoc.sendRequestXML(String ticket, String strHCPResponse, String strCompanyFileName, String qbXMLCountry, Int32 qbXMLMajorVers, Int32 qbXMLMinorVers)

       at QBWebConnector.localhost.WCWebService.sendRequestXML(String ticket, String strHCPResponse, String strCompanyFileName, String qbXMLCountry, Int32 qbXMLMajorVers, Int32 qbXMLMinorVers)

       at QBWebConnector.SOAPWebService.sendRequestXML(String wcTicket, String HCPResponseXML, String cfn, String qbNationality, Int32 qbXMLMajorVers, Int32 qbXMLMinorVers)

       at QBWebConnector.WebService.do_sendRequestXML(Int32 x, String wcTicket, String HCPResponseXML, String cfn, String qbNationality, Int32 qbXMLMajorVers, Int32 qbXMLMinorVers, Boolean& timeout)

    Saturday, September 15, 2018 1:21 AM

Answers

  • User1120430333 posted

    IMO, you should be using a DBcontext with a 'using' statement for each individual query that opens the context, uses the datareader and disposes all of it within the context of the 'using' statement.  

    Secondly, you are reading the records twice. The first read is done in the Linq query.  And the second read of all the records are done again as it iterates the loop, becuase the connection is not closed to disconnect the collection from the database. So, if 100,000 records are read on the query resulting in 100,000 objects in the collection, then as the loop is iterated, 100,000 more individual reads from the database are done again for each object in the collection resulting in 200,000 times you read the same data for a connected collection of objects  due to not disposing of the context to disconnect the collection. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 15, 2018 6:02 AM
  • User1120430333 posted

    The example code shows how to make a List<T>, the results of the Linq query are populated to the List<T>, the connection is closed and disposed resulting in a disconnected collection of objects.                                         

    var addOwners = new List<Owners>(); //it's using whatever the name of the virtual class name is on the model. It could be Owner. 
    
    using (dbContexta = new VTEntities1())
    {
        addOwners = (dbContexta.Owners.Where(o => o.EnteredByProviderId == theprovider && o.AddOwner == true /*&& o.ListId == null*/)).ToList();
    }
    
    foreach (var item in addOwners)
    {
         //do CustomerAdd
         var custAdd = new XmlRqCreate().CustomerAddRq(item, i.ToString());
         i++;
         req.Add(custAdd);
     }
    
    
    using (dbContextb = new VTEntities1())
    {
    
    }
    
    using (dbContextc = new VTEntities1())
    {
    
    }
    
    etc. and etc.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 15, 2018 2:28 PM

All replies

  • User1120430333 posted

    IMO, you should be using a DBcontext with a 'using' statement for each individual query that opens the context, uses the datareader and disposes all of it within the context of the 'using' statement.  

    Secondly, you are reading the records twice. The first read is done in the Linq query.  And the second read of all the records are done again as it iterates the loop, becuase the connection is not closed to disconnect the collection from the database. So, if 100,000 records are read on the query resulting in 100,000 objects in the collection, then as the loop is iterated, 100,000 more individual reads from the database are done again for each object in the collection resulting in 200,000 times you read the same data for a connected collection of objects  due to not disposing of the context to disconnect the collection. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 15, 2018 6:02 AM
  • User379720387 posted

    Understood. Not entirely clear how to fix it.

    This WebService class consists of four regions (GlobalVariables, Constructor, AutoGeneratedMethods, and WebMethods)

    Within WebMethods there a 12 or so functions, where in 3 I am interacting with the db.

    Authentication:

    open db to do a WebSecurity.Login on the provided username and password

    if successfull ==> var dbContext = new VTEntitities1() AND

    var dbContext = new VTEntities1();
                    var theProvider = dbContext.Providers.Where(p => p.UserProfile.Email == strUserName && p.hasQbWc == true).FirstOrDefault();
                    theprovider = theProvider.ProviderId;
                    bool addOwners = dbContext.Owners.Any(o => o.EnteredByProviderId == theProvider.ProviderId && o.AddOwner == true);
                    bool verifyOwners = dbContext.Owners.Any(o => o.EnteredByProviderId == theProvider.ProviderId && o.VerifyCustomer == true);
                    bool updateOwners = dbContext.Owners.Any(o => o.EnteredByProviderId == theProvider.ProviderId && o.UpdateCustomer == true);
                    bool addTxns = dbContext.Transactions.Any(t => t.ProviderId == theProvider.ProviderId && t.AddTransaction == true);
                    bool verifyTxns = dbContext.Transactions.Any(t => t.ProviderId == theProvider.ProviderId && t.VerifyTransaction == true);

    for the purpose of determining if work needs to be done.

    If work is to be done then in SendRequestXML I am building the XML requests and again a new dbContext with the code from the original post.

    In ReceiveResponseXML I am parsing the XML returned again a new dbContext.

    This all works locally, but in live it fails.

    Will this design suffice and eliminate the records being read twice?

    using (dbContext = new VTEntities1())
                    {
                    var addOwners = dbContext.Owners.Where(o => o.EnteredByProviderId == theprovider && o.AddOwner == true /*&& o.ListId == null*/);
                    foreach (var item in addOwners)
                    {
                        //do CustomerAdd
                        var custAdd = new XmlRqCreate().CustomerAddRq(item, i.ToString());
    
                        i++;
                        req.Add(custAdd);
                    }
                }

     

    Saturday, September 15, 2018 1:49 PM
  • User1120430333 posted

    The example code shows how to make a List<T>, the results of the Linq query are populated to the List<T>, the connection is closed and disposed resulting in a disconnected collection of objects.                                         

    var addOwners = new List<Owners>(); //it's using whatever the name of the virtual class name is on the model. It could be Owner. 
    
    using (dbContexta = new VTEntities1())
    {
        addOwners = (dbContexta.Owners.Where(o => o.EnteredByProviderId == theprovider && o.AddOwner == true /*&& o.ListId == null*/)).ToList();
    }
    
    foreach (var item in addOwners)
    {
         //do CustomerAdd
         var custAdd = new XmlRqCreate().CustomerAddRq(item, i.ToString());
         i++;
         req.Add(custAdd);
     }
    
    
    using (dbContextb = new VTEntities1())
    {
    
    }
    
    using (dbContextc = new VTEntities1())
    {
    
    }
    
    etc. and etc.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 15, 2018 2:28 PM
  • User379720387 posted

    Thanks.

    All working now.

    Sunday, September 16, 2018 1:44 AM