Prepared Statements And The Built-in Connection Pool In .net

I have a long-running service with several threads calling the following method hundreds of times per second: void TheMethod() { using (var c = new SqlConnection('...')) {

Solution 1:

Suggest taking a slightly modified approach. Close your connection immedately after use. You can certainly re-use your SqlConnection.

The work being done at //some code may take a long time. Are you interacting with other network resources, disk resources, or spending any amount of time with calculations? Could you ever, in the future, need to do so? Perhaps the intervals between executing statement are/could be so long that you'd want to reopen that connection. Regardless, the Connection should be opened late and closed early.

using (var c = new SqlConnection("..."))
{
    c.Open();
    PrepareAndExecuteStatement1(c, args);
    c.Close();
    // some code
    c.Open();
    PrepareAndExecuteStatement2(c, args);
    c.Close();
    // more code
}

Open Late, Close Early as MSDN Magazine by John Papa.

Obviously we've now got a bunch of code duplication here. Consider refactoring your Prepare...() method to perform the opening and closing operations.

Perhaps you'd consider something like this:

using (var c = new SqlConnection("..."))
{
    var cmd1 = PrepareAndCreateCommand(c, args);

    // some codevar cmd2 = PrepareAndCreateCommand(c, args);

    c.Open();
    cmd1.ExecuteNonQuery();
    cmd2.ExecuteNonQuery();
    c.Close();
    // more code
}