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

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 2

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