Clever Geek Handbook

SQL Server: By default, how long does a connection stay open if I forget to close it

Let's pretend that

SqlDataReader oDR = ...
...
oDR.Read()
...

forgot to close!

Question: How long will the connection stay open by default? Can I change this?

+3


source to share


2 answers


Until the garbage collector cleans it up.

This means: some indefinite period of time.



Which means: Don't do this, always close it when you're done with it.

+5


source


The answer is a little more complicated. In general, ADO.NET uses union. Thus, it improves performance by avoiding the need to open and close connections that are slow on both the client and server side.

As long as what's going on with your connection, it will get deleted when the GC collects it. Non-deterministic. You cannot control it.

But getting rid of it is not the same as closing the connection, but making it available again in the pool.



You can manage the connection pool in the connection string: Connection Pool for .NET Framework Data Provider for SQL Server

This allows you to control the pool size, lifetime, etc.

In C # code, the best practice for disposable objects like DB connections is to create it inside a block using

that will dispose of your object even in the event of an unhandled exception.

+1


source






More articles:


All Articles