how to use RetryLogicProvider for connection lost during command

Question

I'm using SqlRetryLogicBaseProvider to define my retries for a SQL Azure .Net Framework 4.8 win forms application. I'm just starting to understand the underlying mechanisms by performing simple disconnection tests. I also have some customers with a very unstable internet, so loosing the internet connection when performing a command might be an issue. So I added error code 11001 to the transient error list. However, I just don't get the right way to configure the retry logic. If the internet is lost when opening the connection, everything is "fine", connection.open gets retried. But if the internet is lost when perfoming command.ExecuteReader() an error is thrown after the first retry of the command and there is no retry on the connection.

Exception: ExecuteReader requires an open and available Connection. The connection's current state is 'closed'.

Where do I have to handle the connection status? In the retrying event of the SqlRetryLogicBaseProvider? Does it get retried if handled there?

Here is my code:

var options = new SqlRetryLogicOption()
{
     // Tries 5 times before throwing an exception
     NumberOfTries = 5,
     // Preferred gap time to delay before retry
    DeltaTime = TimeSpan.FromSeconds(5),
     // Maximum gap time for each delay time before retry
     MaxTimeInterval = TimeSpan.FromSeconds(60),
     TransientErrors = new List<int> { -1, -2, 0, 109, -2146232060, 596, 1204, 1205, 1222, 49918, 49919, 49920, 4060, 4221, 40143, 40613, 40501, 40540, 40197, 10929, 10928, 10060, 10054, 10053, 997, 233, 64, 18401, 11001 ,1232}
};

 // Create a retry logic provider
 SqlRetryLogicBaseProvider provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options);
 provider.Retrying += (object s, SqlRetryingEventArgs e) =>
 {
     LogRetry(e);
     // handle connection loss here? if so, how?
 }

try
{    
    using (var connection = new SqlConnection(ConnectionString))
    {   
        //retry provider for connection:
        connection.RetryLogicProvider = provider;                  

        // SqlParameter parameter;
        const string selectCmd = "SELECT data FROM mydata WHERE dataID = @dataID";
 
        using (var command = new SqlCommand(selectCmd, connection))
        {                       
            SqlParameter parameter = new SqlParameter("@dataID", SqlDbType.NVarChar, 9);
            parameter.Value = did;
            command.Parameters.Add(parameter);
            // set retry provider for command:
            command.RetryLogicProvider = provider; 
        
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();          
            while (reader.Read())
            {              
                Console.WriteLine("{0}", reader.GetString(0));                
            }
        }
    }
}
catch (Exception ex)
{    
    return String.Empty;
}

EDIT 1: I changed the Retrying event. The RetryLogicProvider is still part of the connection. Problem now is, that the internal retry counter gets lost and is always set to retry number 1, so that my connection gets retried in an endless loop. Couldn't find out how to increase this, yet.

provider.Retrying += (object s, SqlRetryingEventArgs e) =>
{
    LogRetry(e);

    if (s is SqlConnection)
    {
        var conn = s as SqlConnection;
        conn.Close();
        conn.Open();
    }
    else
    {
        if (s is SqlCommand)
        {
            var cmd = s as SqlCommand;
            cmd.Connection.Close();
            cmd.Connection.Open();
        }
    }
};

Answer 1

The issue in your "EDIT 1" is that you call conn.Open() inside SqlConnection retry code. That creates a forever loop. Try to remove that part:

provider.Retrying += (object s, SqlRetryingEventArgs e) =>
{
    LogRetry(e);

    if (s is SqlCommand)
    {
        var cmd = s as SqlCommand;
        cmd.Connection.Close();
        cmd.Connection.Open();
    }
};