Using MySQLConnection in C# does not close properly

Question

Final solution:

The connection was added to the connection pool. So I closed it, but it still remained physically open. With the ConnectionString Parameter "Pooling=false" or the static methods MySqlConnection.ClearPool(connection) and MySqlConnection.ClearAllPools the problem can be avoided. Note that the problem was, that the connection was still alive when I closed the application. Even though I closed it. So either I don't use connection pooling at all or I clear the specific pool before closing the connection and the problem is solved. I'll take my time figuring out what's the best solution in my case.

Thanks to all who answered! It helped my understand the concepts of C# better and I learned alot from the useful input. :)

===

Original Problem:

I've searched for a while now and haven't found the solution for my problem: I'm new to C# and 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?

Any help is appreciated!

Greetings, Simon

P.S.: 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. :(

Solution 1

Im unsure about mysqlconnection but the sql server counter part uses Connection pooling and does not close when you call close instead it puts it in the connection pool!

Edit: Make sure you dispose the Reader, Command, and Connection object!

Edit: Solved with the ConnectionString Parameter "Pooling=false" or the static methods MySqlConnection.ClearPool(connection) and MySqlConnection.ClearAllPools()

Solution 2

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

Solution 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."

Comments

[SLaks]

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

[Skalli]

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.

[SLaks]

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

[Skalli]

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(...)

[Cade Roux]

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.

[SLaks]

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

[Skalli]

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

[Cade Roux]

@user694856 stackoverflow.com/questions/850065/…

[Skalli]

I have tried using it, see my post: link

[Skalli]

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

[Skalli]

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.

[Skalli]

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.

[Garr Godfrey]

@Skalli 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.

[Raven]

@Pecana Happy to be of help!