3

I'm using a sql connection to access to different tables in my database. However the code returns me the below error.

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

MyContext conn = new MyContext()

protected void ChangeName(int id)
{
    User user = conn.MyOtherTable.First(x => x.id == id);

    var elements = conn.MyTable.Where(x => x.id == id && x.name == name).OrderBy(x => x.id).OrderBy(x => x.name).
                    .Select(t => new { t.id, t.name, }).GroupBy(t => new { t.id, t.name, });

                foreach (var item in elements)
                {
                    foreach (var row in item)
                    {
                        for (int j = 1; j <= 5; j++)
                        {
                            if (row.name == "name")
                            {
                                user.name1 = row.name;
                                conn.SaveChanges();
                            }
                            if (row.name == "name2")
                            {
                                user.name2 = row.name;
                                conn.SaveChanges();
                            }
                         }
                     }
            }
 }
Pandian
  • 8,848
  • 2
  • 23
  • 33
Camilla
  • 949
  • 2
  • 14
  • 26

2 Answers2

6

LINQ (when talking to a database) is usually a non-buffered spooling API. To do what you want, either:

  • enable Multiple Active Result Sets (MARS)
  • buffer the data first

I prefer the second option; it just involves adding .ToList() to your first line:

var elements = conn.MyTable.Where(x => x.id == id && x.name == name)
        .OrderBy(x => x.id).OrderBy(x => x.name).
        .Select(t => new { t.id, t.name, }).GroupBy(t => new { t.id, t.name, })
        .ToList();

now after this line has run we know we already have all the data in memory and the reader has closed; previously it could still have been talking row input from the reader.

For completeness, enabling MARS is discussed here - it wouldn't be my recommendation, though.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
0

I count a error as you count, but I had enable Multiple Active Result Sets (MARS),finally I just change IDateReader variable Method from close() to dispose() ,then the problems disappear

qulianqing
  • 11
  • 2