Microsoft.Data.SqlClient retry logic

program.cs

// SQL server connection string
using Dapper;
using Microsoft.Data.SqlClient;

var cs = "Server=.\\test;Database=Northwind;Trusted_Connection=True;";


// Define the retry logic parameters
var options = new SqlRetryLogicOption()
{
    // Tries 5 times before throwing an exception
    NumberOfTries = 5,
    // Preferred gap time to delay before retry
    DeltaTime = TimeSpan.FromSeconds(1),
    // Maximum gap time for each delay time before retry
    MaxTimeInterval = TimeSpan.FromSeconds(20),
    TransientErrors = new List<int> { -1, -2, 0, 109, 233, 997, 1222, 10060, -2146232060, 596 }

};


// Create a retry logic object
SqlRetryLogicBaseProvider provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options);

provider.Retrying += (object s, SqlRetryingEventArgs e) =>
{
    int attempts = e.RetryCount + 1;
    Console.ForegroundColor = ConsoleColor.Yellow;
    Console.WriteLine($"attempt {attempts} - current delay time:{e.Delay} \n");
    Console.ForegroundColor = ConsoleColor.DarkGray;
    if (e.Exceptions[e.Exceptions.Count - 1] is SqlException ex)
    {
        Console.WriteLine($"{ex.Number}-{ex.Message}\n");
    }
    else
    {
        Console.WriteLine($"{e.Exceptions[e.Exceptions.Count - 1].Message}\n");
    }

    // It is not a good practice to do time-consuming tasks inside the retrying event which blocks the running task.
    // Use parallel programming patterns to mitigate it.
    if (e.RetryCount == provider.RetryLogic.NumberOfTries - 1)
    {
        Console.WriteLine("This is the last chance to execute the command before throwing the exception.");
        Console.WriteLine("Press Enter when you're ready:");
        Console.ReadLine();
        Console.WriteLine("continue ...");
    }
};

var connection = new SqlConnection(cs);
// Assumes that connection is a valid SqlConnection object 
// Set the retry logic provider on the connection instance
connection.RetryLogicProvider = provider;
// Establishing the connection will retry if a transient failure occurs.
await connection.OpenAsync();

var sql = "SELECT * FROM Customers";
var list = await connection.QueryAsync<Customer>(sql);

foreach (var item in list)
{
    Console.WriteLine($"customer {item.CustomerID} {item.ContactName}");
}

Console.ReadLine();

record Customer(string CustomerID, string ContactName, string City);