How to make Dapper resilient for SqlAzure?

Question

I recently found out Entity Framework has a very easy way to make connections resilient in SQL Azure. Is there a recommended way to accomplish the same in Dapper?

Answer 1

The fastest way to protect against connection issues in C# against Azure is the Microsoft Transient Fault Handling Block.

For example the below code would retry upto 3 times with 1 second intervals in-between when attempting to open a connection to a Windows Azure SQL Database:

var retryStrategy = new FixedInterval(3, TimeSpan.FromSeconds(1));

var retryPolicy = 
  new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);

retryPolicy.ExecuteAction(() => myConnection.Open());

FixedInterval is the back off policy, so it will try, wait 1 second, try again, etc until it's tried 3 times.

SqlDatabaseTransientErrorDetectionStrategy is simply does a check on the exception thrown, if it's a connection exception that should be retried, it will tell the RetryPolicy to execute the action again. If it is not a connection exception, then the action will not be executed and the original exception will be thrown as normal.

As for when you should use it with Dapper; you can safely retry opening connections and read operations, however be aware or retrying write operations as there is a risk of duplicating inserts, trying to delete a row twice etc.

More detail here, this library can be found as a NuGet Package here which includes the detection strategies for Windows Azure.