Exception: There is already an open DataReader associated with this Connection which must be closed first

Question

I have below code and I am getting exception:

There is already an open DataReader associated with this Connection which must be closed first.

I am using Visual Studio 2010/.Net 4.0 and MySQL for this project. Basically I am trying to run another SQL statement while using data reader to do my other task. I am getting exception at line cmdInserttblProductFrance.ExecuteNonQuery();

SQL = "Select * from tblProduct";

//Create Connection/Command/MySQLDataReader
MySqlConnection myConnection = new MySqlConnection(cf.GetConnectionString());
myConnection.Open();
MySqlCommand myCommand = new MySqlCommand(SQL, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
myCommand.Dispose();

if (myReader.HasRows)
{
    int i = 0;
    // Always call Read before accessing data.
    while (myReader.Read())
    {
        if (myReader["frProductid"].ToString() == "") //there is no productid exist for this item
        {
            strInsertSQL = "Insert Into tblProduct_temp (Productid) Values('this istest') ";
            MySqlCommand cmdInserttblProductFrance = new MySqlCommand(strInsertSQL, myConnection);
            cmdInserttblProductFrance.ExecuteNonQuery(); //<=====THIS LINE THROWS "C# mySQL There is already an open DataReader associated with this Connection which must be closed first."
        }
    }
}

Answer 1

You are using the same connection for the DataReader and the ExecuteNonQuery. This is not supported, according to MSDN:

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

Updated 2018: link to MSDN

Answer 2

Always, always, always put disposable objects inside of using statements. I can't see how you've instantiated your DataReader but you should do it like this:

using (Connection c = ...)
{
    using (DataReader dr = ...)
    {
        //Work with dr in here.
    }
}
//Now the connection and reader have been closed and disposed.

Now, to answer your question, the reader is using the same connection as the command you're trying to ExecuteNonQuery on. You need to use a separate connection since the DataReader keeps the connection open and reads data as you need it.

Answer 3

Just use MultipleActiveResultSets=True in your connection string.

Answer 4

Add MultipleActiveResultSets=true to the provider part of your connection string example in the file appsettings.json

"ConnectionStrings": {
"EmployeeDBConnection": "server=(localdb)\\MSSQLLocalDB;database=YourDatabasename;Trusted_Connection=true;MultipleActiveResultSets=true"}

Answer 5

You are trying to to an Insert (with ExecuteNonQuery()) on a SQL connection that is used by this reader already:

while (myReader.Read())

Either read all the values in a list first, close the reader and then do the insert, or use a new SQL connection.

Answer 6

The issue you are running into is that you are starting up a second MySqlCommand while still reading back data with the DataReader. The MySQL connector only allows one concurrent query. You need to read the data into some structure, then close the reader, then process the data. Unfortunately you can't process the data as it is read if your processing involves further SQL queries.

Answer 7

This exception also happens if you don't use transaction properly. In my case, I put transaction.Commit() right after command.ExecuteReaderAsync(), did not wait with the transaction commiting until reader.ReadAsync() was called. The proper order:

1. Create transaction.
2. Create reader.
3. Read the data.
4. Commit the transaction.

Answer 8

You have to close the reader on top of your else condition.

Answer 9

In my case, I was awaiting an async call, but in the calling scope, I was not awaiting that method that I was making the call in. So, the calling scope was continuing on while my connection was still open.

called scope:

protected override async Task AfterProcessing()
{
    var result = await Stats.WriteAsync();
    Log.Information("Stopping");
}

calling scope:

public virtual async Task Run()
{
    BeforeProcessing();
    try
    {
        Process();
    }
    finally
    {
        AfterProcessing(); // this line was missing an "await"
    }
}

Answer 10

There is another potential reason for this - missing await keyword.

Answer 11

Set multipleactiveresultsets=true in your connectionstring

Multiple Active Result Sets (MARS) is a feature that allows the execution of multiple batches on a single connection. In previous versions, only one batch could be executed at a time against a single connection. Executing multiple batches with MARS does not imply simultaneous execution of operations.

Here's a Link