I just had an interesting discussion with a coworker of mine and wanted to write down my thoughts on the matter. The question revolved around whether it was preferred to call Close() or Dispose() on a SqlConnection. This discussion obviously extends to pretty much any IDisposable/Closeable class but for clarity I'll focus on the SqlConnection object. His question was spurred on by a snippet from the MSDN documentation that states that the Close() method 'is the preferred method of closing any open connection.' His question goes a bit deeper than this seemingly inocuous question. The SqlConnection seems to go counter to some other MSDN documentation that indicates that Close() should call Dispose() where Close() is a more natural means of shutting down an object.
To begin, let's take a peek at what our good friend IL DASM has to say on the matter. Opening up the IL for the SqlConnection.Dispose() we see the following:
...
IL_000a: ldloc.0
IL_000b: switch (
IL_0020,
IL_001a)
IL_0018: br.s IL_0020
IL_001a: ldarg.0
IL_001b: call instance void System.Data.SqlClient.SqlConnection::Close()
IL_0020: ldarg.0
IL_0021: ldnull
IL_0022: stfld class System.Data.SqlClient.SqlConnectionString System.Data.SqlClient.SqlConnection::_constr
...
As we can see, when Dispose() is called, it first does a check to see if the connection to the database is open (an extraneous check in my book, but oh well) and then it calls Close() if the connection were open in the first place. Afterwards, it nulls out the connection string. Now we all know that once an object is disposed it should no longer be used. An advantage to calling Close() is that you can in effect reopen the connection on the same SqlConnection instance. When Close() is called (or Dispose() by virtue of what it does) the connection is not actually closed but is returned to the database connection pool - remember that the connection pool is a collection of SQL Connections, not SqlConnection objects.
When deciding on whether you should call Close() or Dispose() you may be persuaded by language constructs to elect one over the other in certain circumstances. For instance, as a devoted C# developer I find the using() {...} pattern very persuasive. The advantage of this is you can in essence avoid having to call Dispose() as it will automatically be cleaned up for you:
string getUserName(string id) {
using ( SqlConnection cn = new SqlConnection(“...“) ) {
// open the connection
// execute scalar to retrieve the name
// return the name
}
}
Okay, that may be a bit simplistic but it gets the idea across. If you're using a different language, say, Visual Basic you'll have to jump through other hoops:
Function getUserName(ByVal id As String)
Dim cn As SqlConnection
Try
cn = New SqlConnection(“...“)
' open the connection
' execute scalar to retrieve the name
' return the name
Finally
cn.Dispose()
End Try
End Function
Obviously I have neither of these snippets of code have been tested - I typed them in here and not within the IDE.
As for a recommendation, it's hard to say. I definitely have a preference, however: use Dispose(), especially for one-off, autonomous database operations. Ok, I guess the recommendation would be to call at least one of the functions - don't not call one. If you have to choose one over the other, call Dispose(). At the end of the day, however, this probably won't be the straw that broke the camel's back...code it how you see fit for your circumstances and how you like.