Injecting ILogger into Dapper Polly Retry Policy in C#

However, one limitation with extension methods is that you can't inject dependencies because they reside in static classes, and static classes don't have constructors for dependency injection.

A simple solution to this issue is to create a base class for your Dapper repository classes. This allows you to inject dependencies like ILogger and use Polly for retry policies.

You'll need the following libraries for the solution:

• Dapper
• Dapper.Contrib
• Polly
• Polly.Contrib.WaitAndRetry

Additionally, you'll need to reference SqlServerTransientExceptionDetector, which can either be copied into your project or accessed via the NuGet package:

• Microsoft.EntityFrameworkCore.SqlServer

Assuming you're using standard dependency injection and already have an ILogger to work with, you can create a base class like this:

using Microsoft.Extensions.Logging;
using Polly.Retry;
using Polly;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;
using Dapper;

namespace MyApp
{
    public class DapperRetryPolicyBaseClass<T>
    {
        private readonly ILogger<T> _logger;
        private static readonly IEnumerable<TimeSpan> _retryTimes = new[] 
        {
            TimeSpan.FromSeconds(5),
            TimeSpan.FromSeconds(10),
            TimeSpan.FromSeconds(15)
        };

        private readonly AsyncRetryPolicy _asyncRetryPolicy;

        public DapperRetryPolicyBaseClass(ILogger<T> logger)
        {
            _logger = logger;

            _asyncRetryPolicy = Policy
                .Handle<SqlException>(SqlServerTransientExceptionDetector.ShouldRetryOn)
                .Or<TimeoutException>()
                .OrInner<Win32Exception>(SqlServerTransientExceptionDetector.ShouldRetryOn)
                .WaitAndRetryAsync(_retryTimes, (exception, timeSpan, retryCount, context) =>
                {
                    _logger.LogWarning(exception, "{InstanceType} SQL Exception. retry #{RetryCount}. Exception {Exception}",
                        _logger.GetType(), retryCount, exception);
                });
        }

        protected async Task<int> ExecuteAsyncWithRetry(IDbConnection connection, string sql, object param = null,
                                                         IDbTransaction transaction = null, int? commandTimeout = null,
                                                         CommandType? commandType = null) =>
            await _asyncRetryPolicy.ExecuteAsync(async () => await connection.ExecuteAsync(sql, param, transaction, commandTimeout, commandType));

        protected async Task<IEnumerable<T>> QueryAsyncWithRetry<T>(IDbConnection connection, string sql, object param = null,
                                                                    IDbTransaction transaction = null, int? commandTimeout = null,
                                                                    CommandType? commandType = null) =>
            await _asyncRetryPolicy.ExecuteAsync(async () => await connection.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType));
    }
}

By creating this base class, you gain the ability to inject the ILogger and define the Polly retry policy in the constructor.

Using the Base Class

To use the base class, simply inherit from it and define the class type. The ILogger of the base class will automatically match the type of the derived class:

using System.Data.SqlClient;
using Dapper;
using Microsoft.Extensions.Logging;

namespace MyApp
{
    public class MyRepository : DapperRetryPolicyBaseClass<MyRepository>
    {
        private readonly ILogger<MyRepository> _logger;

        public MyRepository(ILogger<MyRepository> logger) : base(logger)
        {
            _logger = logger;
        }

        public async Task Execute(string someDateToBeInsertedIntoATable)
        {
            using var sqlConnection = new SqlConnection("your-connection-string");
            await base.ExecuteAsyncWithRetry(sqlConnection, "INSERT INTO some_table (field) VALUES (@field)", 
                new { Field = someDateToBeInsertedIntoATable });
        }
    }
}

In this example, MyRepository inherits from DapperRetryPolicyBaseClass and passes the ILogger<MyRepository> to the base class constructor. The retry methods from the base class are then used for executing SQL commands, and any retry attempts will be logged using the injected ILogger.