Take the 2-minute tour ×

I have my business-logic implemented in simple static classes with static methods. Each of these methods opens/closes SQL connection when called:

public static void AddSomething(string something)
    using (SqlConnection connection = new SqlConnection("..."))
        // ...

But I think avoiding opening and closing a connection saves performance. I made some tests loooong time ago with OleDbConnection class (not sure about SqlConnection), and it definitely helped to work like this (as far as I remember):

//pass the connection object into the method
public static void AddSomething(string something, SqlConnection connection)
    bool openConn = (connection.State == ConnectionState.Open);
    if (!openConn) connection.Open();
    // ....
    if (!openConn) connection.Close();

So the question is - should I choose the method (a) or method (b) ? I read on another stackoverflow question that connection pooling saved performance for me, I don't have to bother at all...

PS. It's an ASP.NET app - connections exist only during a web-request. Not a win-app or service.

share|improve this question
Just an advise: Use DbConnection.StateChange event to monitor changes in connection's state change (and may be store locally) instead of checking DbConnection.State property directly. It will save you performance cost. –  decyclone Dec 14 '10 at 13:17
One detail that is missing is how this method is part of a page request. Is it the only method called or is it, as I assumed in my response, one of many methods thats is called in a page reqest, it affects which answer is right ;) –  David Mårtensson Dec 14 '10 at 13:24
David - LOTS of methods like this are being called:) –  jitbit Dec 14 '10 at 17:49

5 Answers 5

up vote 28 down vote accepted

Stick to option a.

The connection pooling is your friend.

share|improve this answer
IMHO - he should'nt even do close. the dispose will do it. –  Royi Namir Jan 7 '13 at 20:11
@RoyiNamir I kinda like the call to close the connection. Especially for beginners and newcomers to a code base. It's more explicit and readable. –  edhedges Jul 25 '14 at 19:39

method (a), every time. When you start scaling your application, the logic that deals with the state will become a real pain. Connection pooling does what it says on the tin. Just think of what happens when the application scales, and how easy would it be to manage the connections. The connection pool does a fine job. If you worried about performance think about some sort of memory cache mechanism so that nothing gets blocked.

share|improve this answer
I'd upvote you +50 if only I could :-) –  marc_s Dec 14 '10 at 13:24

Always close connections as soon as you are done with them, so they underlying database connection can go back into the pool and be available for other callers. Connection pooling is pretty well optimised, so there's no noticeable penalty for doing so. The advice is basically the same as for transactions - keep them short and close when you're done.

It gets more complicated if you're running into MSDTC issues by using a single transaction around code that uses multiple connections, in which case you actually do have to share the connection object and only close it once the transaction is done with.

However you're doing things by hand here, so you might want to investigate tools that manage connections for you, like DataSets, Linq to SQL, Entity Framework or NHibernate.

share|improve this answer
You should not normaly open and close a connection within every method call, only once for each page request. Thats what I have learned at least ;) Opening and closing costs time. –  David Mårtensson Dec 14 '10 at 13:11
@David Martensson - connections are not actually opened and closed when you call SqlConnection.Open. ASP.NET recycles active connections from the pool when the connection string matches a previously used connection string. The overhead involved in this is inconsequential, and additionally, trying to "do it yourself" means you have to assume all the management tasks of ensuring the connection is still active for each subsequent use, which adds complexity and overhead. With connection pooling, best practice is to open and close it for every use. –  Jamie Treworgy Dec 14 '10 at 13:23
With all my respect, the answer "Always close connections" does not fit the question very well... I do close them. The question is - when. –  jitbit Dec 14 '10 at 17:52

Disclaimer: I know this is old, but I found an easy way to demonstrate this fact, so I'm putting in my two cents worth.

If you're having trouble believing that the pooling is really going to be faster, then give this a try:

Add the following somewhere:

using System.Diagnostics;
public static class TestExtensions
    public static void TimedOpen(this SqlConnection conn)
        Stopwatch sw = Stopwatch.StartNew();

Now replace all calls to Open() with TimedOpen() and run your program. Now, for each distinct connection string you have, the console (output) window will have a single long running open, and a bunch of very fast opens.

If you want to label them you can add new StackTrace(true).GetFrame(1) + to the call to WriteLine.

share|improve this answer

There are distinctions between physical and logical connections. DbConnection is a kind of logical connection and it uses underlying physical connection to Oracle. Closing/opening DbConnection doesn't affect your performance, but makes your code clean and stable - connection leaks are impossible in this case.

Also you should remember about cases when there are limitations for parallel connections on db server - taking that into account it is necessary to make your connections very short.

Connection pool frees you from connection state checking - just open, use and immediately close them.

share|improve this answer

Your Answer


