Does End Using close an open SQL Connection

Question

If I wrap a SQLConnection in a Using, should I close it or does the end using handle it?

using cn as new system.data.sqlclient.sqlconnection()
    cn.open
    '{do a bunch of other stuff with commands and datareaders here}
    cn.close 'Do I need this?
end using

score 96 · Accepted Answer · answered Dec 17 '08 at 21:19

96

Exiting a using block calls .Dispose() on the object in question (cn in your example) which for a SqlConnection will close the connection and any open resources.

answered Dec 17 '08 at 21:19

matt b

138,234
66
282
345

3

What will happen if an exception is thrown within the using block? will the connection still get disposed? – Youssef May 21 '10 at 19:46
10

Joel Coehoorn's YES. is ambiguous. the full answer is : "You don't need to close because the end using handle it" – Michael Freidgeim Jun 29 '11 at 00:06
1

AFAIK, it does call .Dispose() on it, but it will be closed only if connection pooling is not active. With connection pooling the connection usually returns to the pool, or gets closed if some "clean-up" criteria are met, so you can say nothing in general.. as a matter of fact from your point of view it is "closed", since it becomes available to the pool for the next one needing one.. – Luke May 26 '15 at 13:54

score 31 · Answer 2 · answered Dec 17 '08 at 21:25

31

More precisely calling Dispose or Close will mark the underlying physical connection as "Not in use" - but doesn't really close it. A "Not in use" connection that isn't yet physically closed is thus available for pooling. Therefore - calling Dispose would return a connection to the connection pool.

answered Dec 17 '08 at 21:25

Darin Dimitrov

1,023,142
271
3,287
2,928

1

Sorry.. you say calling Dispose or Close "doesn't really close it", then you say "calling Dispose would return a connection to the pool". Can you clarify? a) Does Dispose both close it *and* return it to the pool? b) Does Close also return it to the pool after closing it? Or will it only go back to the pool after a Dispose? – ingredient_15939 May 23 '12 at 15:36
2

Why does the wrong answer has been checked ? it is clearly known that close/dispose doesn't actually close the connection back bring it back to the pool. – Royi Namir May 25 '14 at 13:26

score 13 · Answer 3 · answered Feb 28 '11 at 17:08

According to MSDN you don't need the close statement.

"The following example creates a SqlConnection, opens it, displays some of its properties. The connection is automatically closed at the end of the using block." -- http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close.aspx

score 4 · Answer 4 · answered Dec 17 '08 at 21:33

4

While the SQL's Dispose method does close the connection (eventually according to darin) you should leave the call to Close in there. The reason is that you would be relying on the underlying implementation of Dispose to call close. Also seeing an Open without a Close is like seeing a New without a Delete for those of us that have programmed in unmanaged languages. It's a code smell for me.

answered Dec 17 '08 at 21:33

Bryan Anderson

15,969
8
68
83

I agree with the code smell. I like to program explicitly, not implicitly. – Astra Dec 17 '08 at 22:54
24

I disagree. It's just noise. Learn the new paradigm with using blocks. – TrueWill Aug 18 '10 at 20:17
I hate the saying "code smell" I do not know why people think it is ok to use lol. In my opinion you are being anal retentive, the using statement closing bracket expresses the Disposing of the resource the using statement uses. You could be argue the explicit Close call adds 3 lines to the code file, possibly causing the using statement block to be large enough that you can no longer see the first line of the using block in a code editor and this can be more inconvenient to the developers delicate sensibilities than an explicit Close statement because there is an Open statement – Brian Ogden Jun 24 '17 at 22:34

score 2 · Answer 5 · answered Feb 04 '16 at 17:09

"A Using block behaves like a Try...Finally construction in which the Try block uses the resources and the Finally block disposes of them. Because of this, the Using block guarantees disposal of the resources, no matter how you exit the block. This is true even in the case of an unhandled exception, except for a StackOverflowException."
https://msdn.microsoft.com/en-us/library/htd05whh.aspx

score 2 · Answer 6 · answered Dec 17 '08 at 21:27

using is just a shorthand to try/finally. this is equivilent code to what you posted

Try
    SqlConnection cn as new system.data.sqlclient.sqlconnection()
    cn.open
    '{do a bunch of other stuff with commands and datareaders here}
    cn.close 'Do I need this?
Finally
    cn.Dispose()
End Try

Dispose is supposed to take care of all resource cleanup, in the case of connections it will close it.

Does End Using close an open SQL Connection

6 Answers6

Linked