[ACCEPTED]-.NET, the SqlConnection object, and multi-threading-connection-pooling

Accepted answer
Score: 30

The obvious solution is to just re-create 10 the SqlConnection object every time a database 9 call requires one - in this case, it would 8 never be shared. Is there any reason not 7 to do this?

On the contrary, that's absolutely what 6 you should do. That's the behaviour SqlConnection was 5 designed for. You should use a Using statement to automatically 4 close the connection at the end of the block 3 you're using it for, and the connection 2 pool mechanism will automatically handle 1 the real underlying connections to the database.

Score: 4

I see no reason NOT to create a SQL connection 8 every time you need it. In fact, that is 7 probably the best way to do it because it 6 gives the .NET framework the flexibility 5 to manage and reuse connections most efficiently. Wrap 4 each of your SQL connections in a USING 3 so you hang on to them as short a time as 2 possible.

We've created a method that creates 1 a connection and everyone uses that:

using (var conn = GetConnection())
    using (var proc = GetProcedure(conn, "procname"))
        using (var reader = proc.GetReader())
            ... DB stuff

More Related questions