DapperExtensions.cs

public static class DapperExtensions
{
    public static readonly AsyncRetryPolicy RetryPolicy = GetPolicy;

    internal static IEnumerable<TimeSpan> GenerateRetries(int maxRetries = 5, int maxSeconds = 26)
    {
        return Enumerable.Range(1, maxRetries)
            .Select(_ => TimeSpan.FromSeconds(new Random().Next(maxSeconds - 5, maxSeconds + 5)));
    }

    private static AsyncRetryPolicy GetPolicy =>
        Policy
            .Handle<SqlException>(SqlServerTransientExceptionDetector.ShouldRetryOn)
            .Or<TimeoutException>()
            .OrInner<Win32Exception>(SqlServerTransientExceptionDetector.ShouldRetryOn)
            .WaitAndRetryAsync(GenerateRetries(),
                (exception, timeSpan, retryCount, context) =>
                {
                    Log.ForContext("MethodName", "MaintenanceRepository")
                        .Error(exception,
                            "Transient fault detected: applying retry-policy [{RetryCount} in {TimeSpan} sec(s)]",
                            retryCount, timeSpan);
                });

    internal static async Task<SqlMapper.GridReader> QueryMultipleAsyncWithRetry(this IDbConnection cnn, string sql,
        object param = null, IDbTransaction transaction = null, int? commandTimeout = null,
        CommandType? commandType = null)
    {
        return await RetryPolicy.ExecuteAsync(async () =>
            await cnn.QueryMultipleAsync(sql, param, transaction, commandTimeout, commandType));
    }

    public static async Task<IEnumerable<T>> QueryAsyncWithRetry<T>(this IDbConnection cnn, string sql,
        object param = null,
        IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return await RetryPolicy.ExecuteAsync(async () =>
            await cnn.QueryAsync<T>(new CommandDefinition(sql, param, transaction, commandTimeout, commandType,
                
                CommandFlags.Buffered)));
    }

    public static async Task<int> BulkInsertAsyncWithRetry<T>(this IDbConnection connection, string sql,
        IEnumerable<T> insertParams, IDbTransaction transaction = null, int? batchSize = null,
        CancellationToken cancellationToken = default(CancellationToken))
    {
        return await RetryPolicy.ExecuteAsync(async () =>
            await connection.BulkInsertAsync(sql, insertParams, transaction, batchSize, cancellationToken));
    }
}