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?

Minor issue: after `using (var myConnection`, you don't need to explicitly `.Dispose()` of your connection. That is handled by `using`. — Hans Keﬆing, Mar 26 '18 at 07:52
Even if the code needs only 100ms, always open and close the connection in the `Execute`-method with the `using`-statement. The `Execute`-method is `public`, you have no control over it's usage. There's no guarantee that connections are disposed if you don't ensure it in `Execute`. The connection-pooling will ensure that no real physical connections must be openend/closed. — Tim Schmelter, Mar 26 '18 at 07:53
@TimSchmelter So are you saying that i should open and close connection for each iteration as compared to opening connection only once? — I Love Stackoverflow, Mar 26 '18 at 07:54
@TimSchmelter - or make the Execute method private/internal. — H H, Mar 26 '18 at 07:59
What is missing here is if there is a Transaction involved. If not, opening separate connections is the cleanest and least error-prone method. — H H, Mar 26 '18 at 08:00
@HenkHolterman: and then someone else changes it later to `public`. However, maybe it would really be a good idea to restrict it's access by making `Execute` private, especially if transactions are involved. `Do` can stay public. _" If not, opening separate connections is the cleanest and least error-prone method"_ The connection-pool doesn't open separate connections if it can (re-)use the same. — Tim Schmelter, Mar 26 '18 at 08:04
It also depends on implementation of `Execute`. Say it first makes a query to database which completes in 100ms. Then it perfoms heavy calculation on data received from database, that takes 2 minutes. In such case both options are wrong, and you should open connection inside `Execute` and close it right after you made database query, so that it's not stayed open for nothing during 2-minute heavy calculation execution. — Evk, Mar 26 '18 at 08:08

CodeFuller · Accepted Answer · 2018-03-26T08:18:27.153

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.

Upvoted for your kind efforts towards helping me but can you please put out difference between this 2 approach so that i can understand preferred way? — I Love Stackoverflow, Mar 26 '18 at 07:55
Even if i explicitly dispose off connection with close and dispose still Ado.net will use connection from connection pooling on each of my iteration? — I Love Stackoverflow, Mar 26 '18 at 08:06
Correct. Connection pooling works transparent for the application. Even if you close or dispose (which is basically the same as closing) a connection, the physical connection still remains in the pool. — CodeFuller, Mar 26 '18 at 08:08
So when does Ado.net removes the connection from connection pool? — I Love Stackoverflow, Mar 26 '18 at 08:12
I've updated my post with answer to this question, please check. — CodeFuller, Mar 26 '18 at 08:19
Thank you so much for helping me and for your valuable time.Please keep helping like this.Appreciated :) — I Love Stackoverflow, Mar 26 '18 at 09:57

Proper way to open and close connection in loop

1 Answers1