Dapper Retry With Polly

While Entity Framework Core has a connection resiliency feature to retry failed database commands, Dapper doesn’t. Ben Hyrman wrote a nice article SQL Server Retries with Dapper and Polly to show us how they use Polly to support this. By following the article, I wrote a tiny sample project for my future reference.

SqlServerTransientExceptionDetector

SqlServerTransientExceptionDetector is a static class to check if the exceptions are from transient errors. The main exception it checks is SqlException. Microsoft maintains two versions of SqlException currently: Microsoft.Data.SqlClient and System.Data.SqlClient. This sample project uses Microsoft.Data.SqlClient.

Retry Policy

private const int RetryCount = 4;
private static readonly Random Random = new Random();
private static readonly AsyncRetryPolicy RetryPolicy = Policy
    .Handle<SqlException>(SqlServerTransientExceptionDetector.ShouldRetryOn)
    .Or<TimeoutException>()
    .OrInner<Win32Exception>(SqlServerTransientExceptionDetector.ShouldRetryOn)
    .WaitAndRetryAsync(
        RetryCount,
        currentRetryNumber => TimeSpan.FromSeconds(Math.Pow(1.5, currentRetryNumber - 1)) + TimeSpan.FromMilliseconds(Random.Next(0, 100)),
        (currentException, currentSleepDuration, currentRetryNumber, currentContext) =>
        {
#if DEBUG
            Debug.WriteLine($"=== Attempt {currentRetryNumber} ===");
            Debug.WriteLine(nameof(currentException) + ": " + currentException);
            Debug.WriteLine(nameof(currentContext) + ": " + currentContext);
            Debug.WriteLine(nameof(currentSleepDuration) + ": " + currentSleepDuration);
#endif
        });

The retry policy adds some randomness to the exponential backoff in case of high concurrency.

Garage.Polly.Extensions.Dapper

The nuget package is pushed to nuget.org with an Azure Pipeline which you can find in the repository.

Sample project

I have also created a sample project to test the nuget package: Garage.Polly.Extensions.Dapper.Sample. There is a Terraform script to provision an Azure Sql Database for testing. A Fluent Migrator project is added to set up the test tables.