This post originated from an RSS feed registered with .NET Buzz by Peter van Ooijen. Original Post: SqlConnectionpooling is not a license to spill Feed Title: Peter's Gekko Feed URL: /error.htm?aspxerrorpath=/blogs/peter.van.ooijen/rss.aspx Feed Description: My weblog cotains tips tricks and opinions on ASP.NET, tablet PC's and tech in general. Latest .NET Buzz Posts Latest .NET Buzz Posts by Peter van Ooijen Latest Posts From Peter's Gekko

In my previous post, on trying to keep control of the number of open database connections, I proposed a solution which might look appealing but, as the quite constructive comments make clear, has serious drawbacks. Let me take another look at the problem.

The discussion focused on connection pooling. Connection pooling has two sides :

1. Opening and closing one specific connection
2. Pooling multiple connections

Take this snippet of code (indirectly) in a webform

At the last moment the connection is opened, data is read after which the connection is closed again. From the perspective of the database server the connection does not close. Perfmon will tell you that. On the next roundtrip re-opening  is very fast as the actual connection (as managed by the connection pool) is still open. When the connection times out the connection pool manager will close the connection. It is a best practice to have connections in code open as short as possible. How short might be a matter of taste. You can surround your connection access code with an open / close statement pair. Or you might have one open close statement pair on every roundtrip. When it comes to performance that is no big issue, the main point is that you just have to be absolutely sure the connection will be closed. Otherwise the garbage collector is going to close the actual connection to the db. It might take quite some time before that happens. In the meantime you have wasted a connection.

Now take this snippet of code:

Here I have two connections being used mutually exclusive. When these connections connect to the same database, using the same credentials, even when their connections string is exactly equal this code will still cost you 2 open connections, not one. This is not covered by connection pooling. Nobody will write code like this but using components the effect will be the same. Every component used is going to cost you (at least) another connection.

In my first try to address this problem I introduced a static connection which all components would use. This works but the side effect is that I have introduced a global connection whose lifetime is beyond the control of the page who is using it. I agree, that's bad. Before ending on the daily WTF I'll present a less drastic alternative.

Sharing the connection is a responsibility of the basepage. The database layer introduces a helper class which wraps up the connection

The class implements Idisposable to do its cleanup. The page is part of the UI which should have no notion how the connection is managed. The sqlconnection has an internal visibility. The UI layer will not see it, in the data access layer the connection is available.

The basepage maintains one copy of the connection.

It's disposed in the unload. This event always fires also when your page hits an exception.

The connection manager is passed to the constructors of the components.

Now the components on a page do share their connection without introducing any effects beyond the scope of the page. And it really does make quite a difference, my sqlserver is quite happy having to maintain a smaller number of open connections.  Connection pooling is cool, but it is no magic which will do anything for you.