Sunday, January 15, 2006

Due to varying stories over the use of Close() and Dispose() on the various connection objects, readers and so on i have gotten into the habit of calling both when i'm done with an object.

However, i read something during the week and as this morning i started doing some work with the SqlDataReader class, i figured i'd see what was happening and maybe see if i was missing something.I may be the only one, but yes i did find that the result depends on whether you are using a Connection object or a DataReader.

In a connection object, calling Dispose() does close the connection, but also suppresses finalization of the connection mainly because it calls an overridden Dispose() method which releases resources from the connection pool and so doesn't require that the Garbage Collector call finalizer for the object (which is expensive). Note that just calling Close() does not call dispose, but does release the connection from the pool. Hence in just calling close, the object itself will be garbage collected by .Net. There have been some cases where it has been mentioned that not calling dispose caused resource problems when a number of connections were created - connections are expensive, so disposing them is probably a better idea.

In a DataReader, when Close() is called, the object is indeed Closed, but Dispose() is not called. So, this object will live around and wait for the garbage collector to clean it up. If it is a command then with Close() the connection used for this reader is also closed (but not disposed). If it is not a command then the connection remains open and must be closed separately - it is fairly common to see a DataReader returned in a method and the base connection remains open, and will only be closed and disposed by the garbage collector at some point. This can have an obvious impact on resources. Calling Dispose() on the DataReader (that is not using a Command Connection) will just close the datareader, but will not close or release the connection, so the connection has to be closed and disposed of separately.

It is probably best to call Dispose() in this latter case, as Microsoft will potentially extend what is done by Dispose() at somepoint in the future and you could have to re-visit your code (unless it was duplicated in Close()).

In the case of the connection however there seems to be some mixed ideas (too many to point out, just Goolge to find many discussions). Some say Close() and then Dispose() - but others say that the garbage collection may impact performance and so just calling Close() and leaving .Net to manage the GC is a better option.