Proper way to open and close connection in loop

Question

I have a code like below :

public void Do
{
      using (var myConnection = new SqlConnection(connectionString))
      {
           for (int i = 0; i < 4; i++)
           {
               MyProcess.Execute(myConnection);

           }
           myConnection.Close();
           myConnection.dispose();
      }
}
public class MyProcess
{
     public void Execute(SqlConnection myConnection)
     {
          if (myConnection.State == ConnectionState.Closed)
                myConnection.Open();
          //long running code
     }
}

Execute methods sometimes take 5-10 minutes,sometimes run in 1-2 minutes for each iteration.

Now here i am confused that whether i shall open and close connection for each iteration and this will be efficient or whether i open and close connection 1 time only.

But with opening and closing connection once, this will hold resource for each of the iteration and will consume resource.

So i am not getting what should be the proper way to handle this

Can anybody please give me some advice on this?

Answer 1

ADO.NET uses Connection Pooling under the hood. That is why opening a new connection each time should not be an issue. Your call to myConnection.Open() will not actually result in opening of physical connection to the Database each time.

Check also this question. It's author made a measure test for connection opening. I have shown him that time of subsequent calls to DbConnection.Open() approaches to 0.

The most preferred way of using DbConnection is to open it for the minimal time period. If your MyProcess.Execute() has a lot of other non-DB related logic that takes considerable time, you should not keep an opened connection during this execution.

Check also Best Practices for Using ADO.NET article. It has following explicit statement:

High performance applications keep connections to the data source in use for a minimal amount of time, as well as take advantage of performance enhancing technology such as connection pooling.

So when does Ado.net removes the connection from connection pool?

This article provides some internal details on connection pooling:

The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed. Note that a severed connection can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only when they are closed or reclaimed.

This article also provides some details on pooling tuning if it's required. However you should consider such manual configuration only if you experience some performance issues caused by the pooling.