Automatically retry request on SqlException

Question

I have to modify my static class used to process my SQL calls in order to retry the request if there is some specific SqlException (connection lost for example).

Here is my method used to call a stored procedure:

public static int CallExecuteNonQuery(string storedProcName, Action<SqlCommand> fillParamsAction, Action afterExecution, BDDSource source)
{
    int result;

    try
    {
        using (var connection = InitSqlConnection(source))

        using (var command = new SqlCommand(storedProcName, connection))
        {
            if (connection.State == ConnectionState.Closed)
                connection.Open();

            command.CommandType = CommandType.StoredProcedure;

            if (fillParamsAction != null)
                fillParamsAction(command);

            result = command.ExecuteNonQuery();

            if (afterExecution != null)
                afterExecution();
        }
    }
    catch (SqlException sqlExn)
    {
        Logger.Exception(string.Format("SQL CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), sqlExn);
        throw;
    }
    catch (Exception exception)
    {
        Logger.Exception(string.Format("SOFTWARE CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), exception);
        throw;
    }
    return result;
}

Following this link, I'm trying to retry the request as many time as it's configured for.

I got the following code:

public static int CallExecuteNonQuery(string storedProcName, Action<SqlCommand> fillParamsAction, Action afterExecution, BDDSource source)
{
    bool RetryRequest = true;
    int result = 0;

    for (int i = 0; i < Properties.Settings.Default.Request_MaximumRetry; i++)
    {
        try
        {
            if (RetryRequest)
            {
                using (var connection = InitSqlConnection(source))
                using (var command = new SqlCommand(storedProcName, connection))
                {
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    command.CommandType = CommandType.StoredProcedure;

                    if (fillParamsAction != null)
                        fillParamsAction(command);

                    result = command.ExecuteNonQuery();

                    if (afterExecution != null)
                        afterExecution();
                }

                RetryRequest = false;
            }
        }
        catch (SqlException sqlExn)
        {
            if (sqlExn.Errors.Cast<SqlError>().All(x => (x.Class >= 16 && x.Class < 22) || x.Class == 24))
            {
                RetryRequest = true;
                continue;
            }

            Logger.Exception(string.Format("SQL CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), sqlExn);
            RetryRequest = false;
            throw;
        }
        catch (Exception exception)
        {
            Logger.Exception(string.Format("SOFTWARE CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), exception);
            RetryRequest = false;
            throw;
        }
    }
    return result;
}

But my modifications are not perfect. For example, after 3 retry with exception, the code doesn't throw and goes into the continue; section before going out of the loop.

Answer 1

I have created a "RetryPolicy" class for this purpose.

The class:

public struct RetryPolicy<T>
{
    private int mRetryMax;
    private int mRetryWaitSec;

    public RetryPolicy(int retryMax, int retryWaitSec)
    {
        mRetryMax = retryMax;
        mRetryWaitSec = retryWaitSec;
    }

    public T DoWork(System.Func<T> func)
    {
        int retries = 0;

        while (true)
        {
            try
            {
                return func();
            }
            catch when (++retries < RetryMax)
            {
                Thread.Sleep(RetryWaitSec * 1000);
            }
        }
    }

    public int RetryMax
    {
        get
        {
            return mRetryMax;
        }
    }

    public int RetryWaitSec
    {
        get
        {
            return mRetryWaitSec;
        }

        set
        {
            mRetryWaitSec = value;
        }
    }
}

Example usage:

new RetryPolicy<int>(int.MaxValue, 1000).DoWork(() =>
{
  Connect(); return 0;
});

This way you can have one line client code that retries a number of times with a millisecond interval.

You could adjust it to a generic for just catching SQLException or whatever you want. Right now it catches all exceptions.

It's non static so you can cache the RetryPolicy during startup.

RetryPolicy policy = new RetryPolicy<int>(int.MaxValue, 1000);
// later
policy.DoWork(() => { Connect(); return 0; });