ADO.NET Connection Pooling

Today I discovered something fundamentally in ADO.NET that changed my view on creating database connections completely.

In my days of Delphi, PHP and Java, connecting to databases was very straightforward. I got very used to opening a database connection, keeping a reference of that connection and close it regarding the situation.

For PHP, this would mean close it as soon as possible. HTTP is stateless, and therefore I got used to close my connections at the end of every HTTP response.

With ASP.NET, this seems to be the same situation. The ADO.NET API actually provides the same similar method set, and you can find this pattern in many ASP.NET web page examples on the net.

For my current project, which is getting bigger and bigger in code base, code is moved to a library - so not as embedded code in aspx pages, code-behind files, or files in the App_Code folder. So all code is bundled together at one central spot.

However, this is not the case for many database related tasks. A lot of these are spread over separated classes. They all require a database connection, and create one as they need.

Now I bet you already knew that creating a database connection is a resource intensive task. Therefore, you would tend to avoid this as much as possible. From my past experience with other programming languages, I was familiar with the use of a Singleton database connection class. This only instance of the connection class would provide a centralized way to manage your connections, or even reduce it to only one connection.

I planned the design of this class for my ASP.NET website today, and with it, I ran into a problem. Even though I would only have one open connection, this connection would remain open even at times when it was not needed at all. At night, all the users would be sound asleep, but this class would still be putting coals on the fire. Therefore, I'd like to close the connection after a specific idle time.

While searching for this feature in ADO.NET, I ran into connection pooling instead. I couldn't find anything useful about this idle feature, but connection pooling gave me the answer.

Connection pooling, nicely described by William Vaughn in his blog, keeps connections open in the background. It is developed for a scenario where a lot of connections are opened and closed within a short period of time. Just like that is with the processing of ASP.NET web pages. Now the nice thing is, this feature is already enabled by default, and efficiently manages the connections in your ASP.NET pages.

This connection pooling eliminates the need for a centralized connection class pattern. It's "cheap" to open connections with ADO.NET, and might be even more efficient rather than a centralized solution. Imagine a situation with the centralized connection class, where you would have to pipe all your database needs over one single connection. You might have to wait for one operation to finish, before the other can start. Furthermore, you might be keeping a connection open even when you are not using it, a waste of resources.

Therefore my conclusion is, make happily new connections all over the place, close them as soon as possible, and use the same connection string. The connection pooling feature of ADO.NET will do the rest of the magic.

My first working day...

...at this brand new weblog!

With this first post, I'll start off with my day to day findings during professional or semi-professional or non-professional working hours.

Since my current occupation is IT Engineer, this blog will be stuffed with things related to the world that is known as Information Technology. More specifically: software and web development.

Although my Dutch roots, I'll keep this weblog English only. To make it more easy for you, I'll label all my posts, so you can easily filter on your points of interest.

I hope you'll find it interesting to read and if it might have been helpful in any sense to you, please don't hesitate to place a comment! :-)

Bart Verkoeijen