Should I write my own connection pooling scheme?
(Question rewritten again so I can upvote peoples answers)
Should I write my own connection pooling scheme?
(Question rewritten again so I can upvote peoples answers)
I would strongly recommend that you don't put the connection in the cache in the first place.
Whenever you need a connection just open a new one (as late as possible). When you're done just close/dispose it (as early as possible). You're probably aware that the easiest pattern to ensure this behaviour is a using
block.
It's very cheap to open and close connections from the pool, and connection pooling is enabled by default. Let the connection pool handle details such as caching etc: that's what it's there for and there's really no benefit -- and lots of potential pitfalls -- if you do it yourself.
I suggest you leave the request cache alone for handling ADO.NET connections and use connection pooling(which is default). Just do this below anywhere you want and it will close/dispose properly:
using (SqlConnection cn = new SqlConnection(connectionString))
{
using (SqlCommand cm = new SqlCommand(commandString, cn))
{
cn.Open();
cm.ExecuteNonQuery();
}
}