c# - 在 SqlException 上自动重试请求

Question

我必须修改用于处理 SQL 调用的静态类，以便在出现某些特定的 SqlException(例如连接丢失)时重试请求。

这是我用来调用存储过程的方法:

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;
}

正在关注 this link ，我尝试按照配置的次数重试请求。

我得到了以下代码:

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;
}

但是我的修改并不完美。例如，在 3 次异常重试后，代码不会抛出并进入 continue; 部分，然后退出循环。

Answer 1

我为此创建了一个“RetryPolicy”类。

类:

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;
        }
    }
}

示例用法:

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

这样您就可以拥有一行客户端代码，以毫秒为间隔重试多次。

您可以将其调整为通用的，以便仅捕获 SQLException 或您想要的任何内容。现在它捕获所有异常。

它是非静态的，因此您可以在启动期间缓存 RetryPolicy。

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