I have an ASP.NET Core 3.1 Web API project which is leveraging Dapper for database operations. In this case I am trying to leverage Polly to to add resiliency to the methods that are connecting to the database while fetching the data.
I referenced the below article for my POC: https://concurrentflows.hashnode.dev/basic-dapper-resiliency-using-polly
This is my code:
public interface ISqlDapperClient
{
Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
Task<T> ExecuteScalarAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);
Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TThird, TReturn>(string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null);
}
public class SqlDapperClient : ISqlDapperClient
{
private readonly ILogger<SqlDapperClient> logger;
private readonly string _dbConnection;
private readonly IConfiguration _configuration;
private readonly IDBAuthTokenService _dbTokenService;
private readonly IDbConnection connection;
private readonly IAsyncPolicy resiliencyPolicy;
//private AsyncRetryPolicy retryPolicy;
public SqlDapperClient(ILogger<SqlDapperClient> logger, IAsyncPolicy resiliencyPolicy, IConfiguration configuration, IDBAuthTokenService dbTokenService)
{
this.logger = logger ?? throw new ArgumentNullException(nameof(logger));
this.resiliencyPolicy = resiliencyPolicy ?? throw new ArgumentNullException(nameof(resiliencyPolicy));
_configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
// Read the connectionstring
_dbConnection = _configuration[C.VaultKeys.DataDBConnString] ?? _configuration[C.AppKeys.LocalDataDBConn];
_dbTokenService = dbTokenService ?? throw new ArgumentNullException(nameof(dbTokenService));
connection = OpenConnectionWithRetryAsync().Result;
}
/// <summary>
/// Method that returns IDbConnection to connect with database
/// </summary>
/// <returns>IDbConnection</returns>
private async Task<IDbConnection> OpenConnectionWithRetryAsync()
{
var conn = new SqlConnection(_dbConnection)
{ AccessToken = await _dbTokenService.GetTokenAsync() };
await conn.OpenAsync();
return conn;
}
public Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) => ExecuteWithResiliency((s, p, c) => c.ExecuteAsync(s, p, transaction, commandTimeout, commandType), sql, param);
public Task<T> ExecuteScalarAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) => ExecuteWithResiliency((s, p, c) => c.ExecuteScalarAsync<T>(s, p, transaction, commandTimeout, commandType), sql, param);
public Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) => ExecuteWithResiliency((s, p, c) => c.QueryFirstOrDefaultAsync<T>(s, p, transaction, commandTimeout, commandType), sql, param);
public Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) => ExecuteWithResiliency((s, p, c) => c.QueryAsync<T>(s, p, transaction, commandTimeout, commandType), sql, param);
public Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null) => ExecuteWithResiliency((s, p, c) => c.QueryAsync(s, map, p, transaction, buffered, splitOn, commandTimeout, commandType), sql, param);
public Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TThird, TReturn>(string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null) => ExecuteWithResiliency((s, p, c) => c.QueryAsync(s, map, p, transaction, buffered, splitOn, commandTimeout, commandType), sql, param);
private async Task<T> ExecuteWithResiliency<T>(Func<string, object, SqlConnection, Task<T>> connectionFunc, string sql, object param = null, [CallerMemberName] string operation = "")
{
return await resiliencyPolicy.ExecuteAsync(ctx => connectionFunc(sql, param, (SqlConnection)connection), ContextHelper.NewContext((SqlConnection)connection, logger, sql, param, operation));
}
}
SqlResiliencyPolicy:
public static class SqlResiliencyPolicy
{
private static readonly ISet<int> transientNumbers = new HashSet<int>(new[]{40613, 40197, 40501, 49918, 40549, 40550, 1205});
private static readonly ISet<int> networkingNumbers = new HashSet<int>(new[]{258, -2, 10060, 0, 64, 26, 40, 10053});
private static readonly ISet<int> constraintViolationNumbers = new HashSet<int>(new[]{2627, 547, 2601});
public static IAsyncPolicy GetSqlResiliencyPolicy(TimeSpan? maxTimeout = null, int transientRetries = 3, int networkRetries = 3)
{
var timeoutPolicy = Policy.TimeoutAsync(maxTimeout ?? TimeSpan.FromMinutes(2));
var transientPolicy = Policy.Handle<SqlException>(ex => transientNumbers.Contains(ex.Number)).WaitAndRetryAsync(transientRetries, attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)), (ex, _, ctx) => ctx.GetLogger()?.LogWarning(ex, "{@Operation} Encountered Transient SqlException. Params:{@Param} Sql:{@Sql}", ctx.OperationKey, ctx[ContextHelper.ParamContextKey], ctx[ContextHelper.SqlContextKey]));
var networkPolicy = Policy.Handle<SqlException>(ex => networkingNumbers.Contains(ex.Number)).WaitAndRetryAsync(networkRetries, attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)), (ex, _, ctx) =>
{
ctx.GetLogger()?.LogWarning(ex, "{@Operation} Encountered a Network Error. Params:{@Param} Sql:{@Sql}", ctx.OperationKey, ctx[ContextHelper.ParamContextKey], ctx[ContextHelper.SqlContextKey]);
if (ctx.TryGetConnection(out var connection))
SqlConnection.ClearPool(connection);
});
var constraintPolicy = Policy.Handle<SqlException>(ex => constraintViolationNumbers.Contains(ex.Number)).CircuitBreakerAsync(1, TimeSpan.MaxValue, (ex, _, ctx) => ctx.GetLogger()?.LogError(ex, "{@Operation} Encountered a Constraint Violation. Params:{@Param} Sql:{@Sql}", ctx.OperationKey, ctx[ContextHelper.ParamContextKey], ctx[ContextHelper.SqlContextKey]), ctx =>
{
});
var resiliencyPolicy = timeoutPolicy.WrapAsync(transientPolicy).WrapAsync(networkPolicy).WrapAsync(constraintPolicy);
return resiliencyPolicy;
}
}
ContextHelper:
public static class ContextHelper
{
public static readonly string LoggerContextKey = nameof(LoggerContextKey);
public static readonly string SqlContextKey = nameof(SqlContextKey);
public static readonly string ParamContextKey = nameof(ParamContextKey);
public static readonly string ConnectionContextKey = nameof(ConnectionContextKey);
public static Polly.Context NewContext(SqlConnection connection, ILogger logger, string sql, object param, string operationKey)
{
return new Polly.Context(operationKey, new Dictionary<string, object>()
{{ConnectionContextKey, connection}, {LoggerContextKey, logger}, {SqlContextKey, sql}, {ParamContextKey, param}});
}
public static ILogger GetLogger(this Polly.Context ctx) => ctx[LoggerContextKey] as ILogger;
public static bool TryGetConnection(this Polly.Context ctx, out SqlConnection connection) => (connection = ctx[ConnectionContextKey] as SqlConnection) != null;
}
Startup.cs (for DI setup)
public static void AddSqlDapperClient(this IServiceCollection services, string connectionString)
{
services.AddScoped(_ => SqlResiliencyPolicy.GetSqlResiliencyPolicy());
services.AddScoped<ISqlDapperClient, SqlDapperClient>();
}
On running the code at runtime I am getting an error :
Unable to resolve service for type 'Polly.IAsyncPolicy' while attempting to activate 'SqlDapperClient'
Can anyone help me here by providing their guidance?
