views:

36

answers:

2

Should I write my own connection pooling scheme?

(Question rewritten again so I can upvote peoples answers)

+5  A: 

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.

LukeH
@Downvoter: Care to explain why?
LukeH
Because this is not answering the question, it site steps it. It's like answering webservice questions by saying, don't use webservices because bad things could happen. I can't delete the question, so I will reword the whole thing.
MatthewMartin
@MatthewMartin: If all StackOverflow did was answer all of the *wrong* questions that people ask (instead of suggesting that perhaps the poster is barking up the wrong tree entirely), it'd be much worse for it. You can't blame someone for trying to help you address a serious issue with the code you originally posted.
Dan Puzey
"A client has asked me to build and install a custom shelving system. I'm at the point where I need to nail it, but I'm not sure what to use to pound the nails in. Should I use an old shoe or a glass bottle?" http://weblogs.asp.net/alex_papadimoulis/archive/2005/05/25/408925.aspx
LukeH
@Matthew: Can you provide more (ahem) context regarding your requirements. Are you writing custom modules/handlers that need to share these objects?
LukeH
@LukeH An old shoe would be superior in the context of the question--unless a hammer is nearby. On a desert island, shoes are fine.How about COM objects, temporary files that need to be deleted at end of request? Yeah, I know. One should't use COM objects, it's not a good practice to use temporary files, etc.
MatthewMartin
+2  A: 

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();
    }
}
rick schott
I'll assume the @Downvoter is hitting me for the same reasons as @LukeH. Why don't you post an answer since you are so critical?
rick schott