Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using MySQLConnection in C# does not close properly

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

like image 349
Skalli Avatar asked Apr 06 '11 13:04

Skalli


People also ask

Can you use MySQL with C?

The C API provides low-level access to the MySQL client/server protocol and enables C programs to access database contents. The C API code is distributed with MySQL and implemented in the libmysqlclient library.

Can I connect MySQL with C++?

MySQL Connector/C++ 8.0 is a MySQL database connector for C++ applications that connect to MySQL servers. Connector/C++ can be used to access MySQL servers that implement a document store, or in a traditional way using SQL statements.

Is TCL a valid MySQL connector?

The tdbc::mysql driver provides a database interface that conforms to Tcl DataBase Connectivity (TDBC) and allows a Tcl script to connect to a MySQL database.

Does MySQL work with Visual Studio 2022?

You can't work with MySql if you use Visual Studio 2022. You can only use MySql with Visual Studio 2019.


2 Answers

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

like image 95
Peter Avatar answered Oct 03 '22 05:10

Peter


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

like image 26
SLaks Avatar answered Oct 03 '22 03:10

SLaks