Closing(), but not disposing() SQL Connection - effects

Question

I read this question on relating to disposing of SQL connections.

My question is, how bad is it to simply close a sql connection, but not dispose of it? We have a function which is simply closed, but never disposed, and it is used 1000s times a day. Is it better to simply close it, or would it be better to close and dispose of it?

I am aware that dispose() also closes the connection, however I would like to know why close doesn't dispose of the connection.

How hard is it to simply wrap every connection with a using statement? I've made that a habit and use it everywhere. — mellamokb
– mellamokb, Commented Jan 14, 2013 at 15:14
.Close likely doesn't dispose the connection because you could easily call .Open again to reopen the connection. — mellamokb
– mellamokb, Commented Jan 14, 2013 at 15:16
@mellamokb. Given pooling is on you can easily create the connection again and well. The pool won't dispose of it until it's been inactive for 2 minutes (default) — Tony Hopkinson
– Tony Hopkinson, Commented Jan 14, 2013 at 16:45

Oded · Accepted Answer · 2013-01-14 15:16:36Z

4

The important thing about connections is to close them so they are returned to the connection pool.

As such, there is little difference between disposing and closing a connection, so long as you are disciplined about closing and not reusing connections.

However, being in the habit of wrapping the creation of a connection in a using statement means you never forget to close it.

It is a general good idiom to follow - creation of any object that implements IDisposable should be wrapped in a using statement, and as such an idiom it is a good one to follow with connections as well.

answered Jan 14, 2013 at 15:16

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Hopkinson · Accepted Answer · 2013-01-14 15:41:53Z

Depends on whether it goes out of scope or not. If it does it will be closed anyway, and returned the connection pool (given you haven't disabled that). So calling close implicitly or explicitly as you are doing makes your intent clear and makes that connection "instantly" available for reuse in the pool.

Idea was to persuade developers to get in and out of the db quick. Lots of small transactions. Not the old style open up a connection and then hide it so no one else could get at it, just in case it was needed again.

If Connection pooling is on the a using clause is equivalent to Create, open, Close. If it's off then a using clause is equivalent to create, open,close,Dispose.

In either scenario, the real deal is to make sure it goes out of scope. Apart from very rare circumstances the connection should be a local reference and have a lifetime of this particular use. You wouldn't generally instantiate one at run time and make it a property of your mainform for intance.

Collectives™ on Stack Overflow

Closing(), but not disposing() SQL Connection - effects

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related