C# application creating many connection threads, MySQL

Question

My C# app is causing atleast 2-3 continuous 'threads connected' as per MySQL Client Connections' tab in Workbench. I still am not able to understand why this is, even after thawing a parallel thread which executes queries, I was only able to bring continuous 'threads connected' down from 4 to 2-3.

I even checked if I am failing to close a connection after a query is executed, couldn't find a missing connection.close(). Anyways wouldn't be sure of it. I checked general log file I could only find 'connect' instances but not a single 'kill/close' instance.

Moreover, as per the command field in the MySQL Client Connections' tab

• Thread no 1: Sleeps for 1 sec and then reverts back to 0.
• Thread no 2: Sleeps for 30 sec and then reverts back to 0.

I can understand the Thread no 2 as I have a timer that triggers every 30 sec and in which I run queries.

NOTE: Timer 30 sec event is the only place I run queries and General log file too collaborates this.

However, I fail to understand why 2 or 3 continuous connection threads are created.

EDIT (Typical Code I use)

internal bool id_exists(int id)  
    {
        bool to_return = false;
        string myconnection = "datasource=localhost;port=3306;username=root;password=root";
        MySqlConnection myConn = new MySqlConnection(myconnection);     //Connect to MySql

        string Query = " SELECT * FROM database.table;";
        MySqlCommand cmdDatabase = new MySqlCommand(Query, myConn);     //Command for the database

        MySqlDataReader myReader;
        try
        {
            myConn.Open();
            myReader = cmdDatabase.ExecuteReader();

            while (myReader.Read())
            {
                if (myReader.IsDBNull(myReader.GetOrdinal("id"))) { continue; }
                if (myReader.GetInt32("id") == pre_id) { to_return = true; break; }
            }
        }
        catch (MySqlException ex)
        {
            //Incase case of exception
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error 5: " + ex.Message + "\n\nError loc: " + this.GetType().Name + " : " + System.Reflection.MethodBase.GetCurrentMethod().Name + "\n\nStack Trace:\n" + ex.StackTrace);
        }
        myConn.Close();
        return to_return;
    }

2nd EDIT: As @PanagiotisKanavos said ADO.Net has connection pooling which causes many connection threads to be created. Most probably variation in connection strings is the culprit for creating multiple connection threads.

I tried adding Pooling = False as per this MySQL Connection will not close link and now at max only one connection thread shots up when I run the C# app. I even noticed that the performance has deteriorated in comparison to Pooling = True.

Now, if anyone could clarify the following doubt it would be very helpful: Since I need to have different connection string and Connection Pooling is creating many connection threads, it contributes to reaching max_connection limit in no time when using C# app on different machines simultaneously.

Is there any other solution through which I can keep Connection pooling and at the same time avoid going beyond max_connection limit too soon?

Answer 1

Instead of:

MySqlConnection myConn = new MySqlConnection(myconnection);

Use a "using" clause:

using (MySqlConnection myConn = new MySqlConnection(myconnection))
{
...
}

With the closing brace in place of the "myConn.Close()" line.

I'm not certain it will fix your problem, but it will be better coding. What the "using" statement does is close and dispose the connection upon exiting the using, regardless of whether there is an exception or not.

Any time you use a class that implements IDispose, such as the SqlConnection class, you should always use that "using" statement to ensure the object gets properly disposed when you are done with it.