Not calling SqlConnection.Close() in Windows Forms - Why does it work sometimes

A friend of mine was hosting an ASP.NET 2.0 app at home until he moved and I offered to host it on my own Win7/IIS7/SQLE2008r2 server.

When I put the code up on my server, after just a few requests I would get this error:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

First I turned up max pool size to temporarily "fix" the problem. Then I took a closer look at his code. It turned out that he never called SqlConnection.Close(). So I added closing and removed max pool size from all the connection strings, and the problem was solved.

I asked him how he solved the problem, and if he'd somehow increased max pool size per default in his server's default web.config or something. He replied ".NET Garbage Collection". So he was relying on garbage collection to close his database connections, and on his server that worked. But on mine it didn't.

Can anyone explain why? He's on vacation so I don't want to bother him asking him for details on versions etc, but I would guess he was running Win2k8.

Jeff Atwood has a wonderful article about this very thing, I highly recommend this read.

Relying on .NET garbage collection to close SQL Connections is a no-no. SqlConnection.Close(), as well as Dispose() should always be called, but Close() at the very least. Note that the using keyword will take care of disposing objects automatically for you (example).

It's irresponsible and silly to rely on the GC for anything. That's like ignoring memory all together and saying "Oh, when the user runs out of RAM, they can just restart! : D" .

In a nut shell, the code not working on the new deployment site is NOT your fault. In this case, it's the coders for irresponsible SQLConnection handling.

The problem with relying on the garbage collector to close the connections, is that you can't.

If you leave the connection ojbects for the garbage collector to clean up, you have no control over when that happens. The garbage collector will close them eventually, but when that happens depends on a lot of factors, for example how much available memory there is, and how much memory is used. Those factors will be different from one server to another, but they may also change over time, so you can't even count on the garbage collector to always work the same even on the same server.

Different databases react differently on having a lot of unclosed connections. An Access database for example is extremely sensetive to this, not allowing more than 64 connections.

Anyhow, you should always close your connections, and your data readers. Also, you should use using blocks or try...finally blocks to make sure that the connections are really always closed, even if something goes wrong in the code.

It's perfectly fine to leave it to the garbage collector, as long as the rate of garbage collection exceeds the rate at which new connections are being created. But I've never seen anyone use ASP.NET in such a way that this would happen.

The only time you should not call SqlConnection.Dispose is when you absolutely can't. For instance, calling it from the destructor of the class that owns it will randomly throw an exception, so if that's the only hope you have, just leave the connection open.