CODEWITHSUNDEEP
c#sqlsqlconnection
Dewald Swanepoel
Dewald Swanepoel

Reputation: 1681

using (SqlConnection), scope and connection pooling

I've only recently learned about this mechanism of connection pooling and discovered that I had been coding my SQL connections all wrong. I used to maintain a global SQL connection against which all SqlCommands would execute.

So I'm now making large scale changes to my existing code. There were no fewer than 260 SqlCommands referencing the global SqlConnection which I am now busy wrapping with 

using (SqlConnection sqlConnection = new SqlConnection(globally_stored_connection_string))
{
    sqlConnection.Open();
    // SqlCommand comes here
}

I suppose it's still a bit of a paradigm shift I have to make, this business of closing a connection only to open a new one shortly after, trusting the connection pooling to take care of the overhead. With that in mind I need to decide now how to wrap SqlCommands that are called many times inside a loop. Would appreciate your thought on which of the following sections of code are preferred (of course there's much more to my SqlCommands than just this but these are simple examples to illustrate the question).

OPTION A:

using (SqlConnection sqlConnection = new SqlConnection(connection_string))
{
    foreach(int number in numberList)
    {
        using (SqlCommand sqlCommand = new SqlCommand("SQL code here using number from foreach loop", sqlConnection))
        {
            sqlConnection.Open();
            sqlCommand.ExecuteNonQuery();
        }
    }
}

OPTION B:

foreach (int number in numberList)
{
    using (SqlConnection sqlConnection = new SqlConnection(connection_string))
    {
        sqlConnection.Open();
        using (SqlCommand sqlCommand = new SqlCommand("SQL code here using number from foreach loop", sqlConnection))
        {
            sqlCommand.ExecuteNonQuery();
        }
    }
}

Upvotes: 2

Views: 1388

Answers (2)

vcsjones
vcsjones

Reputation: 141648

I think you are missing Option C, which to me would make the most sense:

using (SqlConnection sqlConnection = new SqlConnection(connection_string))
{
    sqlConnection.Open();
    using (SqlCommand sqlCommand = new SqlCommand("SQL code here using number from foreach loop", sqlConnection))
    {
        foreach (int number in numberList)
        {
            //Modify command parameters if needed
            sqlCommand.ExecuteNonQuery();
        }
    }
}

This has the least overhead for executing commands. Constructing a SqlCommand object isn't very expensive, but it isn't free either. Here we can reuse it, if possible.

Upvotes: 7

Szymon
Szymon

Reputation: 43023

I would go for

using (SqlConnection sqlConnection = new SqlConnection(connection_string))
{
    sqlConnection.Open();
    foreach(int number in numberList)
    {
        using (SqlCommand sqlCommand = new SqlCommand("SQL code here using number from foreach loop", sqlConnection))
        {
            sqlCommand.ExecuteNonQuery();
        }
    }
}

You only need to open the connection once in this block of code.

While it's good to close the connection as soon as you're done with it, you don't have to take it to the extreme and open a connection for each transaction. It's enough to open it once if your executing several commands one after another. Even with connection pooling there is some overhead in opening a new connection. It's enough if you don't keep it for too long.

Upvotes: 1

Related Questions