Skip to content

Instantly share code, notes, and snippets.

@hyrmn hyrmn/DapperExtensions.cs
Last active Sep 4, 2019

Embed
What would you like to do?
Extension methods for calling Dapper asynchronously with a Polly retry
public static class DapperExtensions
{
private static readonly IEnumerable<TimeSpan> RetryTimes = new[]
{
TimeSpan.FromSeconds(1),
TimeSpan.FromSeconds(2),
TimeSpan.FromSeconds(3)
};
private static readonly AsyncRetryPolicy RetryPolicy = Policy
.Handle<SqlException>(SqlServerTransientExceptionDetector.ShouldRetryOn)
.Or<TimeoutException>()
.OrInner<Win32Exception>(SqlServerTransientExceptionDetector.ShouldRetryOn)
.WaitAndRetryAsync(RetryTimes,
(exception, timeSpan, retryCount, context) =>
{
LogTo.Warning(
exception,
"WARNING: Error talking to ReportingDb, will retry after {RetryTimeSpan}. Retry attempt {RetryCount}",
timeSpan,
retryCount
);
});
public static async Task<int> ExecuteAsyncWithRetry(this IDbConnection cnn, string sql, object param = null,
IDbTransaction transaction = null, int? commandTimeout = null,
CommandType? commandType = null) =>
await RetryPolicy.ExecuteAsync(async () => await cnn.ExecuteAsync(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) =>
await RetryPolicy.ExecuteAsync(async () => await cnn.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType));
}
public static class SampleCaller
{
public async Task UpsertPerson(string personId, string businessId, string firstName, string lastName, string suffix)
{
await _conn.ExecuteAsyncWithRetry(_upsertSql,
new
{
PersonId = personId,
BusinessId = businessId,
FirstName = firstName.Truncate(50),
LastName = lastName.Truncate(50),
Suffix = suffix.Truncate(10)
});
}
}
// This file comes from:
// https://raw.githubusercontent.com/aspnet/EntityFrameworkCore/master/src/EFCore.SqlServer/Storage/Internal/SqlServerTransientExceptionDetector.cs
// and
// https://github.com/Azure/elastic-db-tools/blob/master/Src/ElasticScale.Client/ElasticScale.Common/TransientFaultHandling/Implementation/SqlDatabaseTransientErrorDetectionStrategy.cs
// With the addition of
// SQL Error 11001 (connection failed)
/// <summary>
/// Detects the exceptions caused by SQL Server transient failures.
/// </summary>
public static class SqlServerTransientExceptionDetector
{
public static bool ShouldRetryOn(SqlException ex)
{
foreach (SqlError err in ex.Errors)
{
switch (err.Number)
{
// SQL Error Code: 49920
// Cannot process request. Too many operations in progress for subscription "%ld".
// The service is busy processing multiple requests for this subscription.
// Requests are currently blocked for resource optimization. Query sys.dm_operation_status for operation status.
// Wait until pending requests are complete or delete one of your pending requests and retry your request later.
case 49920:
// SQL Error Code: 49919
// Cannot process create or update request. Too many create or update operations in progress for subscription "%ld".
// The service is busy processing multiple create or update requests for your subscription or server.
// Requests are currently blocked for resource optimization. Query sys.dm_operation_status for pending operations.
// Wait till pending create or update requests are complete or delete one of your pending requests and
// retry your request later.
case 49919:
// SQL Error Code: 49918
// Cannot process request. Not enough resources to process request.
// The service is currently busy.Please retry the request later.
case 49918:
// SQL Error Code: 41839
// Transaction exceeded the maximum number of commit dependencies.
case 41839:
// SQL Error Code: 41325
// The current transaction failed to commit due to a serializable validation failure.
case 41325:
// SQL Error Code: 41305
// The current transaction failed to commit due to a repeatable read validation failure.
case 41305:
// SQL Error Code: 41302
// The current transaction attempted to update a record that has been updated since the transaction started.
case 41302:
// SQL Error Code: 41301
// Dependency failure: a dependency was taken on another transaction that later failed to commit.
case 41301:
// SQL Error Code: 40613
// Database XXXX on server YYYY is not currently available. Please retry the connection later.
// If the problem persists, contact customer support, and provide them the session tracing ID of ZZZZZ.
case 40613:
// SQL Error Code: 40501
// The service is currently busy. Retry the request after 10 seconds. Code: (reason code to be decoded).
case 40501:
// SQL Error Code: 40197
// The service has encountered an error processing your request. Please try again.
case 40197:
// SQL Error Code: 11001
// A connection attempt failed
case 11001:
// SQL Error Code: 10929
// Resource ID: %d. The %s minimum guarantee is %d, maximum limit is %d and the current usage for the database is %d.
// However, the server is currently too busy to support requests greater than %d for this database.
// For more information, see http://go.microsoft.com/fwlink/?LinkId=267637. Otherwise, please try again.
case 10929:
// SQL Error Code: 10928
// Resource ID: %d. The %s limit for the database is %d and has been reached. For more information,
// see http://go.microsoft.com/fwlink/?LinkId=267637.
case 10928:
// SQL Error Code: 10060
// A network-related or instance-specific error occurred while establishing a connection to SQL Server.
// The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server
// is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed
// because the connected party did not properly respond after a period of time, or established connection failed
// because connected host has failed to respond.)"}
case 10060:
// SQL Error Code: 10054
// A transport-level error has occurred when sending the request to the server.
// (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
case 10054:
// SQL Error Code: 10053
// A transport-level error has occurred when receiving results from the server.
// An established connection was aborted by the software in your host machine.
case 10053:
// SQL Error Code: 1205
// Deadlock
case 1205:
// SQL Error Code: 233
// The client was unable to establish a connection because of an error during connection initialization process before login.
// Possible causes include the following: the client tried to connect to an unsupported version of SQL Server;
// the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum
// allowed connections) on the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by
// the remote host.)
case 233:
// SQL Error Code: 121
// The semaphore timeout period has expired
case 121:
// SQL Error Code: 64
// A connection was successfully established with the server, but then an error occurred during the login process.
// (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
case 64:
// DBNETLIB Error Code: 20
// The instance of SQL Server you attempted to connect to does not support encryption.
case 20:
return true;
// This exception can be thrown even if the operation completed successfully, so it's safer to let the application fail.
// DBNETLIB Error Code: -2
// Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
//case -2:
}
}
return false;
}
public static bool ShouldRetryOn(Win32Exception ex)
{
switch (ex.NativeErrorCode)
{
// Timeout expired
case 0x102:
// Semaphore timeout expired
case 0x121:
return true;
default:
return false;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.