Open() late, Close() early

One of the most common mistakes Web developers do is try to be smart about database connection management. Connections are expensive resources, and it seems like it would make a lot of sense to store an open connection, say, in a Session or Application variable, for later re-use, so that next time you need it, it's already there and ready to use.

Well, don't.

In ADO.NET, there's something called connection pooling that you get for free when you use it. Basically, some very smart and highly specialized people have already figured out very a optimized implementation of connection management. In a nutshell, there is a small set of connections that ADO.NET will keep track of so that reopening a connection with the same connection string is virtually free, which is exactly what developers who don't know about it try to achieve by more simplistic and much less efficient ways. It's ironic that they actually make things worse by trying to improve what's already been optimized. It's a typical example of useless early design for performance optimization. As the old saying teaches us, if it ain't broken, don't fix it.

If you try to store connections, two things will happen.

First, you're clogging the connection pool for other applications by keeping for yourself one or more connections, doing nothing with it most of the time.

Second, you're creating a very small bottleneck for your own application: a single connection can't be used by two threads at the same time, so you're forcing all your threads (which you do have in an ASP.NET application even though it's almost invisible to the casual prograsmmer) to fight for the connection.

There is one simple guideline when dealing with database connections, and it's the title of this post: Open() late, Close() early.

It means that you should open connections as late as possible (just before querying the database is ideal), and close them as soon as you're done with them (ideally just after querying): Open() and Close() are the actions that take away or give back connections from or to the pool. This way, you're taking the connection away from the pool the minimum amount of time, so that it can be reused by other threads and applications as soon as possible.

It also means that your connection strings should be as few as possible, because the pool relies on the connection string to reuse an existing connection or open a new one.

One final advice about DataReaders and DataSets. While DataSets can be a little heavy for some tasks, they have the great advantage of being disconnected objects, which means that they are good citizens with the connection pool and that filling a dataset is something that keeps connections out of the pool for a minimum amount of time. DataReaders, on the other hand, need an open connection the whole time they're open. That's usually fine because they are very fast. But if you misuse them, they can be counter-productive for the reasons I've mentioned above. For example, if you do a whole lot of things not related to data-reading while looping over the DataReader, you're keeping a connection away from the pool. So your thread may be fast, but others and other applications can be dramatically slowed down. So here, you should treat the DataReader as a connection, and Open() late and Close() early. In other words, read the data as fast as possible and close the reader. In a Web application, for example, you can open the reader, DataBind() immediately, and close the reader. Don't open the reader in some early event, and keep it around for the rest of the page lifecycle.

And of course, the worse you could do is to forget to Close() altogether. So your best friend in this matter is the using() {} statement (which is now also available in VB). It will make sure your precious connections are closed in all cases, even if something goes terribly wrong and an exception is thrown while the connection is open (it's equivalent to a try/finally). And it will force you to keep the Open() and Close() in the same scope.