I am implementing Polly to retry SqlConnection errors.

This is the logic I need to implement:

• 1st SqlException - retry after 10 seconds
• Total retrying time is 3 minutes
• After 1st retry, all other retry after 5 seconds

// the following should retry with 5 second delays between tries, up to a 3-minute limit:
Policy timeoutAfter3Minutes = Policy.Timeout(TimeSpan.FromMinutes(3));
Policy retryEveryFiveSeconds = Policy.Handle<Exception>()
                               .Or<TimeoutException>()
                               .Or<SqlException>()
                               .WaitAndRetryForever(sleepDurationProvider: i => TimeSpan.FromSeconds(5)
                               ,(exception, retry, timespan) =>
                               {
                               Console.WriteLine($"Retry count is {retry} and timespan is {timespan} " + $"at {DateTime.Now.ToString("hh.mm.ss.ffffff")}");
                               });

// 1st (1 time only): retry after 10 sec
Policy retryFirstTime = Policy
  .Handle<Exception>()
  .Or<TimeoutException>()
  .Or<DivideByZeroException>()
  .WaitAndRetry(1, sleepDurationProvider: i => TimeSpan.FromSeconds(10),
   (Exception exception, TimeSpan timeSpan, int retryCount, Context _) =>
   {
         Console.WriteLine($"1st retrying SqlConnection on attempt : {retryCount} : 
         {exception.InnerException} " +
         $"in {timeSpan.TotalSeconds} seconds");
   });

// Wrap policy with Timeout Exception every 5 sec until 3 minutes
Policy tryEvery5SecondsUpTo3Minutes = timeoutAfter3Minutes.Wrap(retryEveryFiveSeconds);
 
// Wrap policy 1st retry after 10 seconds and other retries at 5 seconds until 3 minutes
Policy combinePolicy = Policy.Wrap(retryFirstTime, tryEvery10SecondsUpTo5Minutes);

 // Usage
     combinePolicy.Execute(() => reTryDB(conn));


public static void reTryDB(SqlConnection conn)
{
     Console.WriteLine($"Open Sql con at {DateTime.Now.ToString("hh.mm.ss.ffffff")}");

     conn.Open(); 

     string selectCommand = "SELECT TOP 1 * FROM [dbo].[TAbleA]";
     var command = new SqlCommand(selectCommand, conn);

     try
     {
         using (SqlDataReader reader = command.ExecuteReader())
         {
             while (reader.Read())
             {
                 Console.WriteLine(String.Format("{0}", reader[0]));
             }
         }
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.ToString());
     }

     conn.Close();
}

Here it retries every 5 seconds. I can not able to split 1st retry at 10 seconds, and all other retry every 5 seconds until a maximum of 3 minutes.

Can someone give me idea how can I use PollyWrap to combine these 2 policies?

I am implementing Polly to retry SqlConnection errors.

This is the logic I need to implement:

• 1st SqlException - retry after 10 seconds
• Total retrying time is 3 minutes
• After 1st retry, all other retry after 5 seconds

// the following should retry with 5 second delays between tries, up to a 3-minute limit:
Policy timeoutAfter3Minutes = Policy.Timeout(TimeSpan.FromMinutes(3));
Policy retryEveryFiveSeconds = Policy.Handle<Exception>()
                               .Or<TimeoutException>()
                               .Or<SqlException>()
                               .WaitAndRetryForever(sleepDurationProvider: i => TimeSpan.FromSeconds(5)
                               ,(exception, retry, timespan) =>
                               {
                               Console.WriteLine($"Retry count is {retry} and timespan is {timespan} " + $"at {DateTime.Now.ToString("hh.mm.ss.ffffff")}");
                               });

// 1st (1 time only): retry after 10 sec
Policy retryFirstTime = Policy
  .Handle<Exception>()
  .Or<TimeoutException>()
  .Or<DivideByZeroException>()
  .WaitAndRetry(1, sleepDurationProvider: i => TimeSpan.FromSeconds(10),
   (Exception exception, TimeSpan timeSpan, int retryCount, Context _) =>
   {
         Console.WriteLine($"1st retrying SqlConnection on attempt : {retryCount} : 
         {exception.InnerException} " +
         $"in {timeSpan.TotalSeconds} seconds");
   });

// Wrap policy with Timeout Exception every 5 sec until 3 minutes
Policy tryEvery5SecondsUpTo3Minutes = timeoutAfter3Minutes.Wrap(retryEveryFiveSeconds);
 
// Wrap policy 1st retry after 10 seconds and other retries at 5 seconds until 3 minutes
Policy combinePolicy = Policy.Wrap(retryFirstTime, tryEvery10SecondsUpTo5Minutes);

 // Usage
     combinePolicy.Execute(() => reTryDB(conn));


public static void reTryDB(SqlConnection conn)
{
     Console.WriteLine($"Open Sql con at {DateTime.Now.ToString("hh.mm.ss.ffffff")}");

     conn.Open(); 

     string selectCommand = "SELECT TOP 1 * FROM [dbo].[TAbleA]";
     var command = new SqlCommand(selectCommand, conn);

     try
     {
         using (SqlDataReader reader = command.ExecuteReader())
         {
             while (reader.Read())
             {
                 Console.WriteLine(String.Format("{0}", reader[0]));
             }
         }
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.ToString());
     }

     conn.Close();
}

Here it retries every 5 seconds. I can not able to split 1st retry at 10 seconds, and all other retry every 5 seconds until a maximum of 3 minutes.

Can someone give me idea how can I use PollyWrap to combine these 2 policies?

• c#
• .net
• timeout
• polly
• retry-logic

• catch (Exception ex) why is this there? If you want Polly to retry you need to let it catch it. And shouldn't your connection be in a using? – Charlieface Commented 20 hours ago
• You are failing Open SQL Con. Why are you even trying to retry connection? Do you think reconnection is going to work? if you are failing logins retry will not help. The only reason to retry is if the connection closes when it is idle. Using Pooling may solve issue : stackoverflow.com/questions/2451952/… – jdweng Commented 19 hours ago
• Yes sometime reconnection will work. – Mit Jacob Commented 19 hours ago
• @Charlieface I removed try.. catch from function – Mit Jacob Commented 19 hours ago
• @jdweng Yes sometime reconnection will work.. Also not necessary it fail on sqlConnection. It fail anywhere in middle of function. Main my concept is how can i try 1st time after 10 sec and and other retry after 5 sec for total 3 minutes – Mit Jacob Commented 19 hours ago

1 Answer 1

You don't need two retry policies, one can be sufficient. All you need to do is change this line:

WaitAndRetryForever(sleepDurationProvider:
  i => TimeSpan.FromSeconds(5),

To that:

WaitAndRetryForever(sleepDurationProvider:
  i => TimeSpan.FromSeconds(i == 1 ? 10 : 5),

Basically you are saying that you want to retry the action in every 5 seconds unless it is the first retry. In that case 10 seconds sleep penalty should be used.

This single retry policy is enough to achieve your requirements.

Policy timeout = Policy.Timeout(TimeSpan.FromMinutes(3));
Policy retry = Policy.Handle<Exception>()
                               .Or<TimeoutException>()
                               .Or<SqlException>()
                               .WaitAndRetryForever(sleepDurationProvider: i => TimeSpan.FromSeconds(i == 1? 10 : 5)
                               ,(exception, retry, timespan) =>
                               {
                               Console.WriteLine($"Retry count is {retry} and timespan is {timespan} " + $"at {DateTime.Now.ToString("hh.mm.ss.ffffff")}");
                               });

Policy combinedPolicy = Policy.Wrap(timeout, retry);