Using MySQLConnection in C# does not close properly

Question

I try to write a class to make MySql Connections easier. My problem is, after I open a connection and close it. It is still open in the Database and gets aborted.

I'm using the 'using' statement' of course, but the connection is still open and gets aborted after I exit the program.

Here's what my code looks like:

using (DatabaseManager db = new DatabaseManager())
{
using (MySqlDataReader result = db.DataReader("SELECT * FROM module WHERE Active=1 ORDER BY Sequence ASC"))
{
    foreach (MySqlDataReader result in db.DataReader("SELECT * FROM module WHERE Active=1 ORDER BY Sequence ASC"))
    {
        //Do stuff here
    }
}
}

The class Database manager opens the connection and closes it when disposed:

public DatabaseManager()
{
    this.connectionString = new MySqlConnectionStringBuilder("Server=localhost;Database=businessplan;Uid=root;");
    connect();
}
private bool connect()
{
    bool returnValue = true;
    connection = new MySqlConnection(connectionString.GetConnectionString(false));
    connection.Open();
}

public void Dispose()
{
    Dispose(true);
}

public void Dispose(bool disposing)
{
    if (disposing)
    {
        if (connection.State == System.Data.ConnectionState.Open)
        {
            connection.Close();
            connection.Dispose();
        }
    }
    //GC.SuppressFinalize(this);//Updated
}
//Updated
//~DatabaseManager()
//{
//  Dispose(false);
//}

So, I checked it in the debugger and the Dispose()-method is called and executes correctly. What am I missing? Is there something I did wrong or misunderstood?

Just in case, the DataReader()-method (Updated version):

public IEnumerable<IDataReader> DataReader(String query)
    {
        using (MySqlCommand com = new MySqlCommand())
        {
            com.Connection = connection;
            com.CommandText = query;
            using (MySqlDataReader result = com.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
            {
                while (result.Read())
                {
                    yield return (IDataReader)result;
                }
            }
        }
    }

---

Ok, I tried to use the yield return:

foreach (MySqlDataReader result in db.DataReader("SELECT * FROM module WHERE Active=1 ORDER BY Sequence ASC"))
{
    //...
}

And I changed the DataReader-method:

public IEnumerable<IDataReader> DataReader(String query)
    {
        using (MySqlCommand com = new MySqlCommand())
        {
            com.Connection = connection;
            com.CommandText = query;
            using (MySqlDataReader result = com.ExecuteReader())
            {
                while (result.Read())
                {
                    yield return (IDataReader)result;
                }
            }
        }
    }

It works in the way that I can retrieve the data, yet I still have the same problem: The connection isn't closed properly.

Hankhanke

Since you don't have any unmanaged resources, you don't need a finalizer. – Sensorium

That's true. But it's not hurting currently, isn't it? Correct me if I'm mistaken. The main goal is to close unused connections as soon as their are no longer needed. This pattern seemed to be an easy way of achieving this goal. I implemented it the way as it was shown in many examples on the web. Eventually I'll change it to get rid of redundant code. – Hankhanke

It's a performance hit, but otherwise, no. – Sensorium

Answer 1

Make 100% sure that you dispose all objects related to your MySqlConnection like Reader, Command and so on.

If nothing works (this is not recommended) you can turn off pooling using the ConnectionString Parameter "Pooling=false" or the static methods MySqlConnection.ClearPool(connection) and MySqlConnection.ClearAllPools()

Tintinnabulation

Currently I'm using the using-directive for these. See link. Still it's no use. – Hankhanke

This post actually had the answer I was looking for, but I didn't recognized it, because I was unfamiliar with how Connection Pooling works. Now I understand and I solved the problem. So, thanks alot @Petoj. – Hankhanke

@Pecana Happy to be of help! – Tintinnabulation

Disabling connection pooling is a bad idea, it's supposed to hold connections open. – Arabeila

@Arabeila Its more than 10 years later, and yes i agree, don't disable it, but verify that you dispose all related objects like my second edit says. – Tintinnabulation

@Arabeila i changed the answer somewhat – Tintinnabulation

Answer 2

You need to wrap the Command and the DataReader in using statements as well.

Sensorium

Well, but how? If I wrap the MySqlDataReader in an using-block it's only available locally. The method DataReader returns the object to an using block. I'd assume that this would dispose the MySqlDataReader-Object at the end of the using block: using (MySqlDataReader result = db.DataReader(...) – Hankhanke

He won't be able to wrap that because he is returning the MySqlDataReader - I don't think you should dispose the MySqlCommand until you are finished with the MySqlDataReader. He should probably look into a yield return to turn this in to a closure. – Gershon

You're right. You could handle the reader's Disposed event: reader.Disposed += delegate { com.Dispose(); } – Sensorium

Answer 3

According to the mysql docs, the MySQLConnection is not closed when it goes out of scope. Therefore you must not use it inside a using.

Quote... "If the MySqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling MySqlConnection.Close or MySqlConnection.Dispose."

Machismo

Using should call close and dispose when the object goes out of scope. It's the same as using a try catch block and close and dispose it in the finally block. – Hankhanke

@Hankhanke is correct. Leaving scope of a using statement is not the same as losing scope in a language sense. A using statement will call Close. – Brigham

Answer 4

Have a look at using something like this:

private static IEnumerable<IDataRecord> SqlRetrieve(
    string ConnectionString, 
    string StoredProcName,
    Action<SqlCommand> AddParameters)
{
    using (var cn = new SqlConnection(ConnectionString))
    using (var cmd = new SqlCommand(StoredProcName, cn))
    {
        cn.Open();
        cmd.CommandType = CommandType.StoredProcedure;

        if (AddParameters != null)
        {
            AddParameters(cmd);
        }

        using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (rdr.Read())
                yield return rdr;
        }
    }
}

Gershon

I'll take a look into it. Looks quite interesting. Haven't seen a yield return before. – Hankhanke

@user694856 #850565 – Gershon

I have tried using it, see my post: link – Hankhanke