Concurrent users on Application result in MySQL Database Error

Question

I have a C# web application that connects to a MySQL database. When multiple users access the site at the same time we see a "there is already an open datareader associated with this command which must be closed first" error. The application works fine when only one person is accessing the site.

I found multiple articles that sited MultipleActiveResultSets=True in the connection string, but that only applies to SQL Server not MySql.

I traced the error to my runSQL function that handles the bulk of my database queries but am unable to find a solution.

This is a fairly straight forward function, it takes raw sql code, a list of parameters, an enum that translates to one of many possible database connection strings, and a bool that determines if we need to set up a transaction.

I am at a loss.

public DataTable runSQL(string QueryStr, List<MySqlParameter> Parameters, ConnectionType Connection, bool Transaction)
{
    DataTable results = new DataTable();
    MySqlConnection con = new MySqlConnection(getConnection(Connection));
    MySqlTransation trans;
    MySqlCommand command;

    con.Open();

    //if a transaction was requested, tie one to the query
    if(Transaction)
    {
        trans = con.BeginTransaction();
        command = new MySqlCommand(QueryStr, con, trans);
    }
    else
    {
        command = new MySqlCommand(QueryStr, con);
    }

    //if parameters were provided add them to the query
    if(Parameters.Count > 0)
        foreach(MySqlParameter parm in Parameters)
            command.Parameters.Add(parm);

    try
    {
        //send the command and get the results
        MySqlReader rdr = command.ExecureReader();

        //populate the column names
        string columnName;
        Type type;
        foreach(DataViewRow row in rdr.GetSchemaTable().DefaultView)
        {
            columnName = row["ColumnName"].ToString();
            type = (Type)row["DataType"];
            results.Columns.Add(columnName, type);
        }

        //populate the results
        results.Load(rdr);

        //so far so good, close the transaction if one was requested
        if(Transaction)
        {
            command.Transaction.Commit();
        }

        con.Close();
    }
    catch (Exception up)
    {
        //something bad happened, rollback if there was a transaction
        if(Transaction)
        {
            command.Transaction.Rollback();
        }

        con.Close();

        //report the error for handling at source.
        throw up;
    }

    return results;
}

Answer 1

Thanks Travis.

I just solved the problem by making the function static and removing the singleton pattern from the database connection. I built it that way to save memory, but in this case it caused more problem than it solved.