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.

Since you don't have any unmanaged resources, you don't need a finalizer. — SLaks
– SLaks, Commented Apr 6, 2011 at 14:19
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. — Skalli
– Skalli, Commented Apr 6, 2011 at 14:30

Peter · Accepted Answer · 2024-02-23 09:21:28Z

21

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

edited Feb 23, 2024 at 9:21

answered Apr 6, 2011 at 14:01

Peter

38.9k39 gold badges151 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Skalli Over a year ago

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

Skalli Over a year ago

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.

Peter Over a year ago

@Pecana Happy to be of help!

Charlieface Over a year ago

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

Peter Over a year ago

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

|

SLaks · Accepted Answer · 2011-04-06 13:29:05Z

6

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

answered Apr 6, 2011 at 13:29

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

3 Comments

Skalli Over a year ago

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 Over a year ago

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 Over a year ago

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

flobadob · Accepted Answer · 2014-09-04 21:35:36Z

2

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

answered Sep 4, 2014 at 21:35

flobadob

2,8822 gold badges23 silver badges23 bronze badges

2 Comments

Skalli Over a year ago

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.

Geoduck Over a year ago

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

davmos · Accepted Answer · 2015-01-11 16:00:19Z

-1

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;
        }
    }
}

edited Jan 11, 2015 at 16:00

davmos

9,6074 gold badges45 silver badges46 bronze badges

answered Apr 6, 2011 at 13:44

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

3 Comments

Skalli Over a year ago

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

Cade Roux Over a year ago

@user694856 stackoverflow.com/questions/850065/…

Skalli Over a year ago

I have tried using it, see my post: link

Collectives™ on Stack Overflow

Using MySQLConnection in C# does not close properly

4 Answers 4

6 Comments

3 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

3 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related