Best practice for handle connections/queries

Question

Question about C# best practices. I have a class that iterates over 5000 Objects and makes a call to the back end for each of those objects:

Class running loop:

for(Object x in objects) //5000
{
   nonStaticObject.callThisMethod(x.id);
}

DAL layer:

ReturnObject x = null;
using(SQLConnection...)
{
    using(SQLCommand...)
    {
         // run something here
         // if found, instantiate object x
    }
}

I did some profiling because of some high CPU generated from my code (parallel for loops) and the hotspots appear to be (and rightfully so) all the calls I'm making to the database. And because these calls are very frequent, I had some questions.

In my mind, my thought is that I should reuse the connection if possible. Is that best practice? I want to allow the code to handle connection disposing (using the using keyword), how do I set up a connection effectively with this strategy that is usable for all 5000 calls? Should I be creating a connection every call to that method like I am now? (I read somewhere that the ADO connection pool reuses connections automatically, so maybe this is already done in the background?).

Thanks for any help

Answer 1

Usually, with respect to database connections, the policy should be open as late as possible and close as early as possible. So lets say if you have a single statement/query to execute then open the connection before executing the query and close your connection after that.

But, in your case you are executing 5000 queries in a loop, so there is no point in opening/closing the connection 5000 times. Instead utilize the single connection and execute all your queries.

Also, opening and closing connection just returns the connection to .Net connection pool. If there is already an open connection then opening a connection using Conn.Open(); would return that opened connection from the connection pool. See: SQL Server Connection Pooling (ADO.NET) and Creating database connections - Do it once or for each query?