.net - Will SqlConnection get disposed by GC?

Question

Disclaimer: I know IDisposable should be implemented when dealing with unmanaged resources. The rest of the code should be deterministic and do using (...) { } (equivalent of try {} finally { Dispose(); }) to guarantee a cleanup as soon as possible. Also, the GC will not call Dispose(), so the recommended pattern is to override the Finalize() method (in C# using the destructor syntax) which then calls Dispose(). The GC will usually call Finalize() (unless GC.SuppressFinalize() has been called).

Problem: So now that I got that out of the way, I have an odd scenario where I cannot do using (SqlConnection...) { } due to code out of my control. I can usually do a deterministic Dispose(), but can't guarantee it. I used Reflector to disassemble SqlConnection and see that it uses Dispose(), but unless I'm blind there is no finalizer/destructor (Finalize() or ~SqlConnection()). Does that mean that the GC will not "clean up" (send back to the pool) the connection in the odd case I can't? I haven't been able to find anything definitive...

Answer 1

Well, it won't get disposed, as finalisation is not disposal.

There is a finaliser in System.ComponentModel.Component, but its suppressed in SQLConnection's constructor. This is a good idea if you inherit from something with a finaliser that you know with 100% certainty you won't need, but a bad idea otherwise. In this case it's a good idea.

Remember though, that SqlConnection is a wrapper on a "real" connection. Indeed, it's most likely a wrapper on a changing set of objects which represent different connection states. This is part of the mechanism that allows for the "real" connection to be pooled efficiently, as each time you call Open() it obtains the relevant object from the pool, and every time you call Close() (whether directly, by Dispose() or by leaving the scope of a using) it returns it.

Now, remember that only objects that directly hold an unmanaged resource or something otherwise not the GC's concern, need to be finalised. SqlConnection holds an object that may (depending on the state of SqlConnection) be one that holds an unmanaged resource (or indeed, deeper through the nest of classes). There is therefore no need for SqlConnection to itself be finalised. Consider the three possible ways an open SqlConnection can stop being an open SqlConnection:

1. Close() is called. This immediately returns the real connection to the pool (or closes it if there is no pooling).
2. Dispose() is called. This calls Close() with the same effect.
3. The object gets garbage collected.

Now, in the third case, the object holds a reference to the object that has the real connection. It also is the only object that does so. That object is hence also going to be garbage collected. If it has a finaliser (which it probably does, though I'm not going to assume there aren't further clever tricks going on) then that finaliser will cause it to be placed into the finaliser queue, and it will be eventually finalised.

If SqlConnection had a finaliser, the only real effects would be:

1. Potential for buggy code (dealing with finalisable members in finaliser code is fraught, as you don't know whether or not they've been finalised).
2. Potential for slowing things up (the real connection is going to be finalised anyway, at best we're just slowing up the finalisation and GC).
3. Nothing to do here anyway (the real connection will be finalised without any help here).

So, putting a finaliser on the SqlConnection is a lose without a win. Also, your real connection should hopefully be eventually finalised.

This said, it's still far from ideal and still very likely to leak connections. Could you detail precisely why you can't call Close() or dispose yourself? Could the code managing the connection not call close for you (the object should end its days somewhere, and should be closed there)?

Do you need to keep it alive for an IDataReader or an object that feeds from an IDataReader to be allowed to complete? In which case, could you use the CommandBehavior.CloseConnection flag so that closing (or disposing of) the reader closes the connection? This latter case is about the only case where I can recall ever having to let a connection leave scope un-disposed.