How to implement SQL Azure Transient Fault Handling Framework for Dapper?

Question

I'm a vb.net guy and have difficulty reading C#. I compiled the C# Dapper to a DLL and use it my app. My main concern is I think I need to modify the source to integrate by default the Transient Fault Handling Framework for SQL Azure in each SQL query.

I can add the retry logic on the connection level because it is ont top of dapper, but not at the execute query level which is embedded in drapper class.

Anyone has done that yet ?

* UPDATE *

Does using only ReliableSqlConnection on top of Dapper call will handle a retry logic on the execute non query ?

Here is sample code of retry from MS with the transietn fault hanling

using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.AzureStorage;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure;
using System.Data;

...

using (ReliableSqlConnection conn = new ReliableSqlConnection(connString, retryPolicy))
{
conn.Open();

IDbCommand selectCommand = conn.CreateCommand();
selectCommand.CommandText = 
  "UPDATE Application SET [DateUpdated] = getdate()";

// Execute the above query using a retry-aware ExecuteCommand method which 
// will automatically retry if the query has failed (or connection was 
// dropped).
int recordsAffected = conn.ExecuteCommand(selectCommand, retryPolicy);

}

Here is the execute part of Dapper code, same name is used but I guess it is a custom execute function

    private static int ExecuteCommand(IDbConnection cnn, IDbTransaction transaction, string sql, Action<IDbCommand, object> paramReader, object obj, int? commandTimeout, CommandType? commandType)
    {
        IDbCommand cmd = null;
        bool wasClosed = cnn.State == ConnectionState.Closed;
        try
        {
            cmd = SetupCommand(cnn, transaction, sql, paramReader, obj, commandTimeout, commandType);
            if (wasClosed) cnn.Open();
            return cmd.ExecuteNonQuery();
        }
        finally
        {
            if (wasClosed) cnn.Close();
            if (cmd != null) cmd.Dispose();
        }
    }

Answer 1

I would recommend wrapping the retry around Dapper, preferably by using the RetryPolicy.ExecuteAction method. That way both the OPEN call to the connection and the command itself will be retried using the TFH retry policy:

For example:

        SqlRetryPolicy.ExecuteAction(() =>
        {
            // Place Dapper ExecuteCommand here: e.g.
            ExecuteCommand(conn,  trans, ... )
        });