MySQL ExecuteNonQuery throws "There is already an open DataReader associated with this Command which must be closed first" Exception

Question

Hi:

I have multiple processes inserting data to the same table in my database. I am using one database connection for all the processes. When I was trying to run one of the sqlcommand, I got the above exception. I did some researching online and it seems that Mysql server only support one datareader per connection. And since each processes uses executeNonQuery to execute SQL commands in a fast speed, it is possible that the previous datareader was not closed when a new commands get issued.

Is there a way around this problem?

Thanks

Answer 1

The root cause of your error is that (hoping to avoid the obvious), the same connection is being used, and already open/executing. Are they static at the moment? Suggest looking into why the pooling isn't being used or is being limited.

Consider refactoring your code to have using statements. Pseudo, noncompiling example:

private void ExecNonQuery(string sqlStatement)
{          
    using (var conn = new MySqlConnection(connString))
    {
       using (var cmd = new MySqlDataCommand(conn))
       {
           cmd.ExecuteNonQuery(sqlStatement);
       }
    }
}

or

private List<Customer> GetCustomers( )
{
    string sqlStatement = "SELECT ID, Name FROM Customer";                
    using (var conn = new MySqlConnection(connString))
    {
        using (var cmd = new MySqlDataCommand(conn))
        {
           //get a reader and do something
           using (var reader = new MySqlDataReader(sqlStatement))
           { 
               ...
           }
        }
    }
}

By wrapping the creation of those objects in a using statement, you'll be ensuring that your objects are closed and disposed of properly.

I'd suggest that yes, you should be opening a new connection everytime. The ADO.NET Driver for MySQL should use proper connection pooling. You really shouldn't have to think about it.

Why have multiple processes share the same connection? What's the need there?