"open/close" SqlConnection or keep open?

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 DoSomething()
{
    using (SqlConnection connection = new SqlConnection("..."))
    {
        connection.Open();

        // ...

        connection.Close();
    }
}

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

//pass around the connection object into the method
public static void DoSomething(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 in 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.

Solution 1:

Stick to option a.

The connection pooling is your friend.

Solution 2:

Use Method (a), every time. When you start scaling your application, the logic that deals with the state will become a real pain if you do not.

Connection pooling does what it says on the tin. Just think of what happens when the application scales, and how hard would it be to manually manage the connection open/close state. The connection pool does a fine job of automatically handling this. If you're worried about performance think about some sort of memory cache mechanism so that nothing gets blocked.

Solution 3:

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.

Solution 4:

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();
        conn.Open();
        Console.WriteLine(sw.Elapsed);
    }
}

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.

Solution 5:

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.