Is it safe to rely on SqlConnection retry logic while using SqlCommand?

Question

I was using Microsoft.Practice.TransientFaultHandling block for retry logic. Now I switched my application to .Net 4.8 and use the new build in retry logic for SqlConnection. I was wondering if I need a special retry logic for my SqlCommand (I used Polly before) or if this is also build in. There is no possibility to log a retry when relying on the build in functions which makes it really hard to test.

Microsoft states here :

"There is a subtlety. If a transient error occurs while your query is being executed, your SqlConnection object doesn't retry the connect operation. It certainly doesn't retry your query. However, SqlConnection very quickly checks the connection before sending your query for execution. If the quick check detects a connection problem, SqlConnection retries the connect operation. If the retry succeeds, your query is sent for execution."

I tested this by just disconnecting and reconnecting the internet within the retry time range and my command got executed after a while. So it seems to work for this simple scenario. But is it really safe to rely on this or do I still have to implement a retry logic for my SqlCommand?

Here is my code:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString); 
builder.ConnectRetryCount = 5;
builder.ConnectRetryInterval = 3;

MyDataSet m_myDataSet = new MyDataSet();
using (SqlConnection sqlConnection = new SqlConnection(builder.ConnectionString)) 
{
   try
    {
       sqlConnection.Open();
    }
    catch (SqlException sqlEx)
    {
        // do some logging                          
        return false;
    }
    try
    {
        using (SqlCommand cmd = new SqlCommand(selectCmd, sqlConnection))
        {                                         
              using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(m_myDataSet, tableName);
                }                          
        }
    }           
}

Answer 1

The answer to your question is to analyze why your connection to the database is open so long that it is going idle and timing out. The ConnectRetryCount and ConnectRetryInterval properties allow you to adjust reconnection attempts after the server identifies an idle connection failure. I would follow the Microsoft recommendations on this one:

Connection Pooling Recommendation

We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information, see using Statement or How to: Dispose of a System Resource for Visual Basic.

Open your connections and close them when no longer needed like this:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.ConnectRetryCount = 5;
builder.ConnectRetryInterval = 3;

bool updateSuccess = true;

MyDataSet m_myDataSet = new MyDataSet();
using (SqlConnection sqlConnection = new SqlConnection(builder.ConnectionString))
{
    try
    {
        sqlConnection.Open();

        using (SqlCommand cmd = new SqlCommand(selectCmd, sqlConnection))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(m_myDataSet, tableName);
            }
        }
    }
    catch (SqlException sqlEx)
    {
        // do some logging (avoid exceptions here)
        updateSuccess = false;
    }
    finally
    {
        sqlConnection.Close();
    }
}

return updateSuccess;

Hope that helps.

Happy coding!!!