using (SqlConnection), scope and connection pooling

Question

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();
        }
    }
}

Answer 1

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.