Jonas Stawski

Everything .NET and More

SqlConnection: To Close or To Dispose?

A well written program will always clean up it’s resources and with SqlConnection is no different. We should always open, use, close. Some people are paranoid about it and would do something like this:

   1: conn.Close();
   2: conn.Dispose();
   3: conn = null;

Now that’s one dead connection! But is all this necessary? Should we call Close and Dispose? When should we call Close and when should we call Dispose?

Well, if we use the Red Gate’s free Reflector and browse to System.Data.SqlClient.SqlConnection and to the Dispose method we can see that it will in turn call the Close method:

image

The Dispose also clears it from the poolGroup and gets rid of the UserConnectionOptions. So if you are sure you’re not going to use this specific connection object anymore then you are safe to Dispose it which will also take care of the closing.

If you want to reuse the connection object just close it and re open it when necessary.

Comments (4) -

Excellent, thanks for this, very important for good developers.

Reply

This was very helpful as I was one of those paranoid people.  ;)

VS2012 points this out if you use both .dispose and .close

Reply

Still could find resource consumed in sql

Reply

According to Microsoft's own documentation, the CLOSE and DISPOSE commands are "functionally equivalent", and yet all over the web it appears very few developers actually treat these like that.  So who do I trust?  Microsoft, the makers?  Or the thousands of users who imply these are slightly different commands?

Reply

Add comment

biuquote
Loading