C# MySqlConnection won't close

Question

I have an application that fires a mysql command (query) "show databases", the query works and returns properly but I can't close my connections. The user I used had 24 connections allowed at the same time so the problem popped up further down my program but reducing the allowed connections to 2 shows me that I can't even close the first query (which isn't in a loop). The code is the following:

    protected override Dictionary<string, Jerow_class_generator.Database> loadDatabases()
    {
        MySqlConnection sqlCon = new MySqlConnection(this.ConnectionString);
        sqlCon.Open();

        MySqlCommand sqlCom = new MySqlCommand();
        sqlCom.Connection = sqlCon;
        sqlCom.CommandType = CommandType.Text;
        sqlCom.CommandText = "show databases;";

        MySqlDataReader sqlDR;
        sqlDR = sqlCom.ExecuteReader();

        Dictionary<string, Jerow_class_generator.Database> databases = new Dictionary<string, Jerow_class_generator.Database>();
        string[] systemDatabases = new string[] { "information_schema", "mysql" };

        while (sqlDR.Read())
        {
            string dbName = sqlDR.GetString(0);
            if (!systemDatabases.Contains(dbName))
            {
                databases.Add(sqlDR.GetString(0), new MySQL.Database(dbName, this));
            }
        }

        sqlCom.Dispose();
        sqlDR.Close();

        sqlCon.Close();
        sqlCon.Dispose();
        return databases;
    }

P.S. The 'New MySQL.Database(dbName, this));' is my owm made class which only stores the DB structure, could be considered irrelevant.

The exact error I get is 'max_user_connections'. on the connection.open line of the next time a query needs to be fired.

Answer 1

Rather than keeping track of all the Open/Close/Dispose calls all over the place, I'd recommend just replacing all of those with using statements. This will make sure the expected scope of each object is clear and that it will be destroyed/disposed upon exiting that scope.

Answer 2

Close() nor using will help alone with your problem because ADO.NET is using its own connection pooling and connections are by default not closed until program is closed. There are few options to solve this, but consider performance implications and is this really desired behavior for your application.

• Add ";Pooling=False" to your connection string.
• SqlConnection.ClearPool Method
• SqlConnection.ClearAllPools Method

For more information read: SQL Server Connection Pooling (ADO.NET)

Answer 3

Along with the using suggestions above, when creating your sqlDR variable you should use the CloseConnection command behavior to close the actual connection if that is your intended action. As noted in the documentation here.

When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

So your code to instantiate your reader would look like this:

    //to instantiate your variable
    MySqlDataReader sqlDR;
    sqlDR = sqlCom.ExecuteReader(CommandBehavior.CloseConnection);

   //closing your datareader reference here will close the connection as well
   sqlDR.Close();

If you wrap all your code in a using block using the above method, you don't need any of those Close() or Dispose() methods other than the sqlDR.Close();

Answer 4

when use "using" key word what happen is.when the garbage collector activate it first dispose objects which was declred in using statement.

Answer 5

I recommend using connection pooling in combination with the MySqlHelper class, passing the connection string as the first argument. That allows MySQL to open the connection if necessary, or keep it open according to the pooling cfg, without you having to know about it.

Answer 6

I changed my code to use 1 connection and keep it open and when testing I came across an error that a datareader should be closed. Now since all my queries didn't close the dataReader object (I used dataTable.Load(cmd.ExecuteReader()).) I think the problem might be there.

Keeping 1 open connection worked perfectly so I don't know what caused the not closing problem. I gues it was the dataReader not closing by itself.

Answer 7

Close() will definitely help you close your.

 using (MySqlConnection conn = GetConnection())
        {
            conn.Open();
            using (MySqlCommand cmd = conn.CreateCommand())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "UserDetail";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(new Album()
                        {
                            Id = Convert.ToInt32(reader["UId"]),
                            Name = reader["FirstName"].ToString(),
                            ArtistName = reader["LastName"].ToString()

                        });
                    }
                }
            }
        }

In the above code, you can see one if condition before opening the connection it will help you to reuse your already open connections check below code.

if (conn.State != ConnectionState.Open) { conn.Open(); }