Do i need to close a MySqlConnection in asp.net?

Question

I've read that in PHP non-persistent open links are automatically closed at the end of the script's execution. So what's up with asp.net?

Answer 1

Simple: PHP and ASP.NET aren't the same thing. They have different "rules". Don't try to write code in one environment as if it were the other.

In .NET should almost always open a connection as late as you can and close it as early as you can, letting the built-in connection pool handle the "real" connection to the database. Don't leave it to the GC/finalizer to tidy up connections for you.

Answer 2

Yes you need to close open connections to database. ADO.NET has a connection pool, and open connections are reserved to you while it stays opened. When you close it, it will be available to other connection requests.

But if you're using DataAdapter, and you don't open connection manually, you don't need to close it. DataAdapter manages it and closes it's own connection :

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand selectCMD = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
selectCMD.CommandTimeout = 30;

SqlDataAdapter custDA = new SqlDataAdapter();
custDA.SelectCommand = selectCMD;

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

Answer 3

Well, its a good practice to release unmanaged data manually, although the finalizer would take care of it eventually.

Answer 4

ASP.Net is said to be "managed", but that really only refers to memory.

When your page instance goes out of scope your are guaranteed that the memory object which wraps the connection will eventually be collected and cause it to be closed, but you are not guaranteed when that will happen. Since it's much more efficient to release memory in larger batches, your connection may hang around a while, and if you get a lot of these that can cause a big problem.

So, you should make sure your connection is closed.

Answer 5

Since most of the connection objects implement IDisposable and have finalizers you could technically rely on the garbage collector to close the connection for you since the finalizer will call Close() on the connection. Obviously this is a bad idea - wrap your connection objects in using statements or explicitly close them when you are done with them.

Answer 6

The easiest way to do this is with a using block:

var cmd = new MySqlCommand("command text here");
using (var con = new MySqlConnection("connection string")) {
    cmd.Connection = con;
    cmd.Connection.Open();
    //execute command here
}

The connection object will be automatically disposed when you leave the scope.

Answer 7

Since I require 50 reputation to comment on someones post, I am posting my comment to Adam Lassek as an answer. His approcah is the corrct way to go. The Using satement will clean up the unmanaged code.