Pooling MySQL Connections with Microsoft Enterprise Library

Question

My setup is MySql.Data.MySqlClient v6.9.8.0 and Microsoft.Practices.EnterpriseLibrary.Data v6.0.0.

The program is a long running program that runs continuously listening for tasks and then performs the job with some form of database action (depending on what the request was.) Sometimes the requests will be one after the other, sometimes there will be several hours between them.

I've tried using Pooling=true in the connection string but it causes me a lot of problems (not all the time - these are intermittent problems.)

Here is an example:

[MySqlException (0x80004005): Authentication to host 'localhost' for user 'root' using method 'mysql_native_password' failed with message: Reading from the stream has failed.]

Turning off pooling fixes the problem but at the same time it makes the queries slower because we can't reuse connections. I've searched online and a lot of people have this same issue and the only fix/workaround I've found is Pooling=false which I'd rather avoid if possible.

Here is an example of my query code:

Database db = this.GetDatabase(databaseName);

List<dynamic> results = new List<dynamic>();

// Run the sql query
using (DbCommand dbCommand = db.GetSqlStringCommand(query))
{

    foreach (var parameter in inParameters)
    {
        db.AddInParameter(dbCommand, parameter.Key, parameter.Value.Item1, parameter.Value.Item2);
    }

    foreach (var parameter in outParameters)
    {
        db.AddOutParameter(dbCommand, parameter.Key, parameter.Value.Item1, parameter.Value.Item2);
    }

    using (IDataReader dataReader = db.ExecuteReader(dbCommand))
    {
        IDictionary<string, object> instance;

        do
        {
            // Read each row
            while (dataReader.Read())
            {
                instance = new ExpandoObject() as IDictionary<string, object>;

                // Populate the object on the fly with the data
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    instance.Add(dataReader.GetName(i), dataReader[i]);
                }

                // Add the object to the results list
                results.Add(instance);
            }
        } while (dataReader.NextResult());
    }

    return results;
}

Any ideas?

Answer 1

Can you try this? I know, I know. using "using" should mean I don't have to call the dataReader.Close() method...but I still do it. I also slightly altered the dr.Read block.

This guy talks about it.

http://www.joseguay.com/uncategorized/ensure-proper-closure-disposal-of-a-datareader

I know, I know. You shouldn't have to. Even when using Ent library, I do an extra .Close step to try and make sure.

Database db = this.GetDatabase(databaseName);

List<dynamic> results = new List<dynamic>();

// Run the sql query
using (DbCommand dbCommand = db.GetSqlStringCommand(query))
{

    foreach (var parameter in inParameters)
    {
        db.AddInParameter(dbCommand, parameter.Key, parameter.Value.Item1, parameter.Value.Item2);
    }

    foreach (var parameter in outParameters)
    {
        db.AddOutParameter(dbCommand, parameter.Key, parameter.Value.Item1, parameter.Value.Item2);
    }

    using (IDataReader dataReader = db.ExecuteReader(dbCommand))
    {
        IDictionary<string, object> instance;

        while (dataReader.Read())
        {
            instance = new ExpandoObject() as IDictionary<string, object>;

            // Populate the object on the fly with the data
            for (int i = 0; i < dataReader.FieldCount; i++)
            {
                instance.Add(dataReader.GetName(i), dataReader[i]);
            }

            // Add the object to the results list
            results.Add(instance);
        }

        if (dataReader != null)
        {
            try
            {
                dataReader.Close();
            }
            catch { }
        }           

    }

    return results;
}