Error "There Is Already an Open Datareader Associated with This Command Which Must Be Closed First" When Using 2 Distinct Commands

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

An SqlDataReader should be closed after its use, otherwise it keeps the connection locked and you cannot use that connection until you close it.

However you don't need an SqlDataReader to discover if you have already registered an email address

public bool good(string emailadress)
{
    string query = @"IF EXISTS(SELECT 1 FROM Clients WHERE Email = @email) 
                     SELECT 1 ELSE SELECT 0";
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = emailadress;
    int result = (int)cmd.ExecuteScalar();
    return (result == 1);
}

Keep in mind that disposable objects like the SqlCommand, the SqlDataReader but of uttermost importance, the SqlConnection should be created at the moment you need them and disposed immediately to free valuable resouces.

The code above with a connection created on the spot would be:

public bool good(string emailadress)
{

    string query = @"IF EXISTS(SELECT 1 FROM Clients WHERE Email = @email) 
                     SELECT 1 ELSE SELECT 0";
    using(SqlConnection con = new SqlConnection(....here put your connection string...))
    using(SqlCommand cmd = new SqlCommand(query, con))
    {
        con.Open();
        cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = emailadress;
        int result = (int)cmd.ExecuteScalar();
        return (result == 1);
   }
}

Error There is already an open DataReader associated with this Command which must be closed first when using 2 distinct commands

I suggest creating an additional connection for the second command, would solve it. Try to combine both queries in one query. Create a subquery for the count.

while (dr3.Read())
{
    dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verificação
}

Why override the same value again and again?

if (dr3.Read())
{
    dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verificação
}

Would be enough.

There is already an open DataReader associated with this Command which must be closed first error on Entity Framework linq query

Found this answer: Entity Framework: There is already an open DataReader associated with this Command

Which says that it's probably triggered due to nested queries or lazy loading. It sounds like the only way to fix this is to not use lazy loading or to enable MARS (Multiple Active Result Sets).

In your case, it's probably not an actual second connection, but another result set coming from the same connection. I also wonder if it could be due to the ToListAsync().

Actually, now that I look at it again, it could be something with the gr.OrderByDescending() call in the middle of the query? That might be attempting to enumerate the result set in the middle of the outer result set. Maybe try to rewrite the query (even incorrectly) to avoid something like that, and see if the problem goes away.

How to solve there is already an open data reader with this command error?

As coded, the entire operation can be collapsed to just executing an SQL of:

UPDATE shoporder 
SET LaborCost = LaborCost + TireRepairCost, TireRepairCost = 0 
WHERE TireRepairCost > 0

Minimally that would look like:

private void button1_Click(object sender, EventArgs e)
{
  using var c = new SqlCommand("UPDATE shoporder SET LaborCost = LaborCost + TireRepairCost, TireRepairCost = 0 WHERE TireRepairCost > 0", ConnectionString);
  c.Connection.Open();
  c.ExecuteNonQuery();
}

Footnote

If you're going to be doing a lot of database work like this, switching to using Dapper will help you out. Dapper code would be:

private async void button1_Click(object sender, EventArgs e)
{
  using var c = new SqlConnection(ConnectionString);
  await c.ExecuteAsync("UPDATE shoporder SET LaborCost = LaborCost + TireRepairCost, TireRepairCost = 0 WHERE TireRepairCost > 0");
}

..but the real magic of Dapper is where you want to run DB queries and get objects:

using var c = new SqlConnection(ConnectionString);
var orders = await c.QueryAsync<ShopOrder>("SELECT * FROM shopOrders WHERE TireRepairCost > @cost", new{ cost = 0 }).ToList();

Just those two lines would give you a list of ShopOrder collection objects to do stuff with. Doing it "the long way" would be more like:

using var c = new SqlCommand("SELECT * FROM shopOrders WHERE TireRepairCost > @cost", ConnectionString);
c.Parameters.AddWithValue("@cost", 0); //btw, google 'addwithvalue is evil'
c.Connection.Open();
var r = c.ExecuteReader();
var orders = new List<ShopOrder>();
while(r.Read()){
  orders.Add(new ShopOrder {
    Id = r.GetInteger("Id"),
    LaborCost = r.GetInteger("LaborCost"),
    TireCost = r.GetInteger("TireCost"),
  });
}

Painful..

Entity Framework runtime error - There is already an open DataReader associated with this Command which must be closed first

To answer your question, you have your TestContext wrapped in a using statement which means that the TestContext will remain open until the end of the using statement.

Your initial call is essentially a query which only gets executed when your foreach loop is hit:

var students = context.Students.Where(s => s.Id == 1);

This is causing the error. I.e., it is resulting in multiple queries on a DataReader that does not allow it.

@Arman is correct: .ToList() will solve your problem because the query is being executed to get all the students to a list before the foreach loop. Obviously, there are a number of solutions to this problem. Probably the most common one would be set MARS (Multiple Active Result Sets) to true on your connection string; i.e.:

Server={myServer};Initial Context={myDb};MultipleActiveResultSets=True;…

This will allow multiple open DataReaders.

---

Related Topics