Detecting if the garbage collector was invoked (.Net)

Question

When using SqlConnection, it's important to always close it when used - either by .Close() or placing the SqlConnection in a "using". Unfurtunately, people, including myself, tend to forgot that and this is where the garbage collectors rescues me for a while until i've forgot to close my connections too many times or the number of people using the application grows.

I'd like to know, if even possible, how to detect if the garbage collector disposed the SqlConnection because it figured that it wasn't in use anymore or if the SqlConnection was closed the correct way.

Another way could be inheriting SqlConnection and putting a timer on it's initializer and check how long it took for the connection to be closed when disposing the class. I don't really like timers but the idea just came up while writing this.

Maybe there's a third and even smarter way to all this... What would you recommend?

Answer 1

Since SqlConnection is sealed, you won't be able to inherit from it. (And I don't think it is a good idea to do so -- If it was possible, you should probably add your code in Dispose(false), because that is what the finalizer calls).

It is better to detect these kind of problems by using a static code analysis tool, which has the power to detect all places in your code where you forget to dispose a connection. There is one built in in Visual Studio, or you can use FxCop.

To help you not to forget disposing the connection, it is a good idea to:

• Keep all DB connection code in one layer / assembly / module, so it is not scattered through the projects.
• Have utility methods to perform SQL commands and returning the results; so you don't create SQLConnection more places than you need to.
• Remember to utilize the C# using construct.