<update. this is ghost in a rathole There's nothing wrong with connection pooling, there was something wrong with my server.... No need to read further unless case you're interested in writing useless code .. Follow this link and may the text below be a monument of desparate people doing desperate things.>
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 :
- Opening and closing one specific connection
- Pooling multiple connections
Take this snippet of code (indirectly) in a webform
sqlConnection1.Open();
sqlDataAdapter1.Fill(dataSet11);
sqlConnection1.Close();
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:
if (CheckBox1.Checked)
{
sqlConnection1.Open();
sqlDataAdapter1.Fill(dataSet11);
sqlConnection1.Close();
}
else
{
sqlConnection2.Open();
sqlDataAdapter2.Fill(dataSet21);
sqlConnection2.Close();
}
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
public class ConnectionManager : IDisposable
{
private SqlConnection cn = null;
#region IDisposable Members
public void Dispose()
{
if (cn != null)
cn.Dispose();
}
#endregion
internal SqlConnection DBconnection
{
get
{
if (cn == null)
{
cn = new SqlConnection();
cn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings[settingName];
}
return cn;
}
}
}
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.
private ConnectionManager _DBconManager;
internal ConnectionManager DBconManager
{
get
{
if (_DBconManager == null)
_DBconManager = new ConnectionManager();
return _DBconManager;
}
}
It's disposed in the unload. This event always fires also when your page hits an exception.
protected override void OnUnload(EventArgs e)
{
base.OnUnload (e);
if (_DBconManager != null)
_DBconManager.Dispose();
}
The connection manager is passed to the constructors of the components.
public PersoneelComponent(ConnectionManager cm)
{
InitializeComponent();
sqlConnection1 = cm.DBconnection;
}
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.