What happens if I leave the database connection open on an ASP.NET web page

Question

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

Now that the server side page processing is complete, the GC will have all the variables on my page as well as the connection object. But when it is located, the connection that was opened earlier is automatically closed? I mean, when the GC removes the connection object, it automatically closes the connection that was established with the database server; or does it just delete the connection object, and the connection in the database remains open until the connection timeout occurs in the database, and then the database server closes the connection itself?

Answer 1

The MSDN documentation is pretty straightforward:

If SqlConnection leaves scope, it will not be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and remove functionally equivalent.

Use either using blocks to arrange them automatically, or explicitly .Close() . Using blocks are preferred.

By disconnecting connections, your application may end up disconnecting from connections when trying new requests, which will lead to errors. I encountered such a problem in an application that I was debugging. The original developers were not able to close connections explicitly on several pages, and the traffic was high enough for users to start getting errors. I wrapped the abusive connections in the using block, and the problem disappeared.

Answer 2

The connection remains open. If you have many page views and many open connections, you may get 500 errors.

Answer 3

Your connections will not be closed until (and not when) your page object is completed, and this may take some time. It would be very easy to maximize the number of connections available and start getting errors.

Answer 4

You must use using blocks, then you do not have to ask a question:

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

Answer 5

your connection will not be closed if it is not selected by the GC, and if you have many visitors, which leads to many connections, this can be terrible. In addition, if you try to open an open connection, it will cause an error, so you need to check that it is better to either write it to a block or close the connection yourself.