What happens if I leave a database connection open in an ASP.NET web page

Question

Suppose that I have an ASP.NET page. In the page load event handler, I open a database connection and do some processing. But after the processing is done, I don't close the connection explicitly by calling the CLOSE method of the connection object.

Now when the page processing at the server side is finished, the GC will dispose all the variables in my page, and also, the connection object too. But when it is disposed, does the connection that was opened previously is automatically closed? I mean, when GC disposes the connection object, does it automatically close the connection that was established with the database server; or it simply dispose the connection object, and the connection at the database is remained open, until the connection timeout occurs at the database and then the database server closes the connection by itself?

Answer 1

You should use using blocks, then you won't have to ask the question:

using (var conn = new SqlConnection(connectionString))
{
    using (var cmd = new SqlCommand(commandText, conn))
    {
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read()) { /* ... */ }
        }
    }
}

Answer 2

Connection is remained open. If you have lots of pageviews, and lots open connections, you can get 500 error.

Answer 3

The MSDN documentation is pretty clear about this:

If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent.

Either use the using blocks to have it disposed automatically, or explicitly .Close() it. The using blocks are preferred.

By leaving connections open your application may eventually run out of connections when new requests are attempted, resulting in errors. I've faced such a problem in an application I was debugging. The original developers failed to close the connections explicitly on a few pages and traffic was high enough that users started getting errors. I wrapped the offending connections in a using block and the problem went away.

Answer 4

Your connections won't be closed until after (not when) your page object is finalized, and that might be a while. It would be very easy to max out the number of available connections and start getting errors.

Answer 5

your connection won't be closed unless chosen by GC and if you have many visitors resulting in lots of connections then this may be horrible. Also if you try to open an opened connection it will throw error so you have to check for that better is either write it in using block or close the connection yourself.