CODEWITHSUNDEEP
.netsql-server
ren
ren

Reputation: 3993

SQL Server: by default how long connection stays open if I forget to close it

Suppose

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

forgot to close!

Question: for how long connection will stay open by default? Can I change this?

Upvotes: 3

Views: 1376

Answers (2)

JotaBe
JotaBe

Reputation: 39055

The answer is a bit more complicated. In general, ADO.NET uses pooling. This way it boosts performance, by avoiding the need to open and close connections, which is kind of slow on both the client and server side.

As long as what happens with your connection it will be disposed when the GC collects it. Non-deterministic. You can't control it.

But disposing it's not the same as closing the connection, but making it available in the pool again.

You can control connection pooling in your connection string: Connection Pooling for the .NET Framework Data Provider for SQL Server

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

In the C# code sie, the best practice for Disposable objects, like DB Connections, is to create it inside an using block, which will dispose your object, even in the case of an unhandled exception.

Upvotes: 1

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391634

Until the garbage collector cleans it up.

Which translates to: Some indeterminate period of time.

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

Upvotes: 5

Related Questions