CODEWITHSUNDEEP
c#dbcontextdatareader
Keith Clark
Keith Clark

Reputation: 609

MultipleActiveResultSets vs Multiple 'using()' statements

In my controller, I tried retrieving data from a datatable from within a foreach loop using a context defined outside of the foreach loop, but received errors stating that a DataReader was already in use. After searching here on Stackoverflow, I found a post talking about adding MultipleActiveResultSets=true; to my Web.config. That worked.

I also read about wrapping the call in a new context implementing the using() function to dispose of the context after the call, so each pass through the foreach loop creates and then disposes of the context.

This is the code with the using statement

using (var _billingContext = new ContextModel())
{
    var casebilling = _billingContext.Billings.Where(b => b.CaseId == currentcase.Id).ToList();
    casetoadd.Billings = casebilling;
}

Are there advantages one way or the other?

Edited to show original code:

foreach (var currentcase in clientcases)
{
    var billing = new List<Billing>();
    var casetoadd = new CaseViewModel();

    var casebilling = _Context.Billings.Where(b => b.CaseId == currentcase.Id).ToList();
    casetoadd.Billings = casebilling;
    casetoadd.Id = currentcase.Id;
    casetoadd.CaseNumber = currentcase.CaseNumber;
    clientCases.Add(casetoadd);
}

This is what was giving me the error

Upvotes: 1

Views: 332

Answers (1)

RJohn
RJohn

Reputation: 121

MultipleActiveResultSets allows applications to have more than one pending request per connection. In your situation that is not the reason for your error, actually using that without wrapping the context in using statement would cause SQL to keep opening a connection for the same request (Bad practice). Your issue was (I’m assuming because you haven't posted your original code) you were using LINQ with lazy loading (default) and on your call you haven't called the “.ToList” method which execute the SQL call which would give you an in-memory list of your data that you can freely loop through without having to go back to the database. Side note using statement is just best practice of disposing the context but it's not required context will call dispose when your application does garbage-collection.

Upvotes: 1

Related Questions