Is closing a MySQL connection important in a .NET application ? (To be exact, C#)

Question

Some first things that people learned in their early use of MySQL that closing connection right after its usage is important, but why is this so important? Well, if we do it on a website it can save some server resource (as described here) But why we should do that on a .NET desktop application? Does it share the same issues with web application? Or are there others?

Answer 1

If you use connection pooling you won't close the physical connection by calling con.Close, you just tell the pool that this connection can be used. If you call database stuff in a loop you'll get exceptions like "too many open connections" quickly if you don't close them.

Check this:

for (int i = 0; i < 1000; i++)
{
    var con = new SqlConnection(Properties.Settings.Default.ConnectionString);
    con.Open();
    var cmd = new SqlCommand("Select 1", con);
    var rd = cmd.ExecuteReader();
    while (rd.Read())
        Console.WriteLine("{0}) {1}", i, rd.GetInt32(0));
}

One of the possible exceptions:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

By the way, the same is true for a MySqlConnection.

This is the correct way, use the using statement on all types implementing IDsiposable:

using (var con = new SqlConnection(Properties.Settings.Default.ConnectionString))
{
    con.Open();
    for (int i = 0; i < 1000; i++)
    {
        using(var cmd = new SqlCommand("Select 1", con))
        using (var rd = cmd.ExecuteReader())
            while (rd.Read())
                Console.WriteLine("{0}) {1}", i, rd.GetInt32(0));
    }
}// no need to close it with the using statement, will be done in connection.Dispose

Answer 2

Yes I think it is important to close out your connection rather than leaving it open or allowing the garbage collector to eventually handle it. There are a couple of reason why you should do this and below that I'll describe the best method for how

WHY:

So you've opened a connection to the database and sent some data back and forth along this pipeline and now have the results you were looking for. Ideally at this point you do something else with the data and the end results of your application is achieved.

Once you have the data from the database you don't need it anymore, its part in this is done so leaving the connection open does nothing but hold up memory and increase the number of connections the database and your application has to keep track of and possibly pushing you closer to your maximum number of connections limit.

"But wait! I have to make a lot of database calls in rapid succession!"

Okay no problem, open the connection run your calls and then close it out again. Opening a connection to a database in a "modern" application isn't going to cost you a significant amount of computing power/time, while explicitly closing out a connection does nothing but help (frees up memory, lowers your number of current connections).

So that is the why, here is the how

HOW:

So depending on how you are connecting to your MySQL database you a probably using an IDisposible object to help manage the connection. Here is what MSDN has to say on using an IDisposable:

As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called. Within the using block, the object is read-only and cannot be modified or reassigned.

Here is my personal take on the subject:

1. Using a using block helps to keep your code cleaner (readability)
2. Using a usingblock helps to keep your code clear (memory wise), it will "automagically" clean up unused items
3. With a usingblock it helps to prevent using a previous connection from being used accidentally as it will automatically close out the connection when you are done with it.

In short, I think it is important to close connections properly, preferably with a con.close() type statement method in combination with a using block

As pointed out in the comments this is also a very good question/answer similar to yours: Why always close Database connection?