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

如果存在某些特定的 SqlException(例如连接丢失)，我必须修改用于处理 SQL 调用的静态类，以便重试请求。
这是我用来调用存储过程的方法:

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;部分。

最佳答案

我假设您知道自动重试的注意事项，尤其是当这些涉及非幂等操作时。
与其使用局部变量来跟踪成功或失败，我建议直接为此目的使用控制流关键字:

public static int CallExecuteNonQuery(string storedProcName, Action<SqlCommand> fillParamsAction, Action afterExecution, BDDSource source)
{
    int retryCount = 0;   // recoverable exception will be rethrown
                          // when this count reaches limit

    while (true)   // conditions for breaking out of loop inlined
    {
        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);
                var result = command.ExecuteNonQuery();
                if (afterExecution != null)
                    afterExecution();
                return result;   // on success, return immediately
            }
        }
        catch (SqlException sqlExn)
        {
            // if error is recoverable, and retry count has not exceeded limit,
            // then retry operation
            if (sqlExn.Errors.Cast<SqlError>().All(x => (x.Class >= 16 && x.Class < 22) || x.Class == 24)
                && ++retryCount < Properties.Settings.Default.Request_MaximumRetry)
            {
                continue;
            }

            // otherwise, rethrow exception
            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;
        }
    }
}

关于c# - 在 SqlException 上自动重试请求，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35864154/