Alex Rivera | Logout

"open/close" SqlConnection or keep open?

Asked 2010-12-14T12:59:49.033
140

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.

c# sqlconnection
Edit
Report

2 Answers

83

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.

answered 2010-12-14T13:05:00.620
36

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.

answered 2010-12-14T13:02:25.200

Your Answer