ADO.NET SqlData Client connections never go away

Question

An asp.net application I am working on may have a couple hundred users trying to connect. We get an error that the maximum number of connections in the pool has been reached. I understand the concept of connection pools in ADO.NET although in testing I've found that a connection is left "sleeping" on the ms sql 2005 server days after the connection was made and the browser was then closed. I have tried to limit the connection lifetime in the connection string but this has no effect. Should I push the max number of connections? Have I completely misdiagnosed the real problem?

Answer 1

All of your database connections must either be wrapped in a try...finally:

SqlConnection myConnection = new SqlConnection(connString);
myConnection.Open();
try
{
}
finally
{
   myConnection.Close();
}

Or...much better yet, create a DAL (Data Access Layer) that has the Close() call in its Dispose method and wrap all of your DB calls with a using:

using (MyQueryClass myQueryClass = new MyQueryClass())
{
   // DB Stuff here...
}

A few notes: ASP.NET does rely on you to release your Connections. It will release them through GC after they've gone out of scope but you can not count on this behavior as it may take a very long time for this to kick in - much longer than you may be able to afford before your connection pool runs out. Speaking of which - ASP.NET actually pools your connections to the database as you request them (recycling old connections rather than releasing them completely and then requesting them anew from the database). This doesn't really matter as far as you are concerned: you still must call Close()!

Also, you can control the pooling by using your connection string (e.g. Min/Max pool size, etc.). See this article on MSDN for more information.