loop in a db connection or db connection in a loop

Question

I am sure both of them would work. But I am wondering, should I keep the connection open till the datatable loop is done? Or open an connection and close it for each time of the loop to keep the connection short?

foreach (DataRow row in dt.Rows)
{
    using(SqlConnection con = new SqlConnection(conString))
    {
             // do things
    }
}

OR:

using(SqlConnection con = new SqlConnection(conString))
{
     foreach (DataRow row in dt.Rows)
    {
        // do things
    }        
}

Answer 1

There is no real advantage to "keeping the connection short" since you still need the connection and use it across the same time window in either case.

The downside to opening/closing repeatedly is the penalty due to overhead of opening a connection, resulting in a lot of needless churn.

Besides, there is a good chance that the ADO connection pool would keep the connection open anyway, so you probably aren't gaining anything.

If this is a batch process (not in an OLTP environment), then there is nothing wrong with long-lived connections, as long as the code cleans up properly when it is done, and the sooner you get it done, the sooner you aren't tying resources.