Injecting ILogger into Dapper Polly Retry Policy in C#

There are many resources detailing how to implement a retry policy when using Dapper, and one solution I find particularly useful is the extension method approach. However, extension methods reside in a static class, meaning you can't inject any dependencies because there's no constructor for dependency injection.

An easy workaround is to create a base class for your Dapper repository classes, allowing dependency injection and retry logic to work seamlessly.

Before proceeding, ensure you have the following dependencies installed:

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

Additionally, you'll need a reference to SqlServerTransientExceptionDetector. You can either copy the code directly into your class or reference the Microsoft.EntityFrameworkCore.SqlServer NuGet package:

• SqlServerTransientExceptionDetector (raw code to be copied into your solution)
• Microsoft.EntityFrameworkCore.SqlServer NuGet package

Creating the Base Class

This example assumes you're using standard dependency injection and have an ILogger available to inject.

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

namespace Demo
{
    public class DapperRetryPolicyBaseClass<T>
    {
        private readonly ILogger<T> _logger;
        private static readonly IEnumerable<TimeSpan> _retryTimes = new[] 
        {
            TimeSpan.FromSeconds(2),
            TimeSpan.FromSeconds(4),
            TimeSpan.FromSeconds(8)
        };

        private readonly AsyncRetryPolicy _asyncRetryPolicy;

        // Constructor to inject ILogger
        public DapperRetryPolicyBaseClass(ILogger<T> logger)
        {
            _logger = logger;

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

        // Method to execute a Dapper query with retry logic
        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));

        // Method to query data with retry logic
        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));
    }
}

In this example:

• The DapperRetryPolicyBaseClass<T> defines an ILogger dependency.
• The retry policy is created using Polly in the constructor, and we log retries in case of exceptions like SqlException or TimeoutException.
• ExecuteAsyncWithRetry and QueryAsyncWithRetry methods wrap Dapper's ExecuteAsync and QueryAsync methods with retry logic.

By using this base class, you can inject the ILogger into the constructor, and define the Polly retry policy. The retry logic is encapsulated in the constructor and executed within the retry methods.

Using the Base Class

To use the retry functionality, create a repository class that inherits from DapperRetryPolicyBaseClass and leverages the base retry methods.

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

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

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

        // Example method that uses the retry logic
        public async Task InsertDataAsync(string someDateToBeInserted)
        {
            using var sqlConnection = new SqlConnection("YourConnectionString");

            // Use the base class method to execute the SQL query with retry logic
            await ExecuteAsyncWithRetry(sqlConnection, "INSERT INTO SomeTable (DateField) VALUES (@Field)", new { Field = someDateToBeInserted });
        }
    }
}

In this example:

• The MyRepository class inherits from DapperRetryPolicyBaseClass, specifying its own type for the logger.
• The retry policy is automatically handled via the base class, and the logger logs each retry attempt.
• By using this structure, dependency injection for ILogger works seamlessly within your retry logic.

Configure Dependency Injection in Startup (ASP.NET Core)

If you're using ASP.NET Core, make sure to register the services in Startup.cs (or Program.cs in newer versions).

public void ConfigureServices(IServiceCollection services)
{
    // Register logging services
    services.AddLogging();

    // Register your repository
    services.AddScoped<MyRepository>();
}

Using the Repository

Now, you can inject MyRepository wherever needed (e.g., in a controller or service) and use the retry logic with logging.

public class MyService
{
    private readonly MyRepository _repository;

    public MyService(MyRepository repository)
    {
        _repository = repository;
    }

    public async Task ProcessData()
    {
        await _repository.InsertDataAsync("2025-03-11");
    }
}

By following this approach, you can easily inject ILogger into your Dapper Polly Retry Policy, allowing you to log retries and handle transient errors efficiently. The base class provides a reusable mechanism for Dapper operations with retry logic, making your code more maintainable and scalable.