SqlCommand.RetryLogicProvider 屬性

定義

命名空間:

Microsoft.Data.SqlClient

組件:

Microsoft.Data.SqlClient.dll

套件:

Microsoft.Data.SqlClient v5.2.0

取得或設定值，其指定繫結至這個命令的 SqlRetryLogicBaseProvider 物件。

public:
 property Microsoft::Data::SqlClient::SqlRetryLogicBaseProvider ^ RetryLogicProvider { Microsoft::Data::SqlClient::SqlRetryLogicBaseProvider ^ get(); void set(Microsoft::Data::SqlClient::SqlRetryLogicBaseProvider ^ value); };

public Microsoft.Data.SqlClient.SqlRetryLogicBaseProvider RetryLogicProvider { get; set; }

member this.RetryLogicProvider : Microsoft.Data.SqlClient.SqlRetryLogicBaseProvider with get, set

Public Property RetryLogicProvider As SqlRetryLogicBaseProvider

屬性值

SqlRetryLogicBaseProvider

當設定為 null (預設) 時，將會使用預設不可重試的提供者。

備註

您必須先設定這個屬性的值，才能執行命令才會生效。

若要套用重試邏輯，請先執行下列步驟，再執行命令：

1. 使用 SqlRetryLogicOption 類型定義組態參數。
2. SqlRetryLogicBaseProvider使用 類別的下列其中一個靜態方法來 SqlConfigurableRetryFactory 建立 ：
   • CreateFixedRetryProvider
   • CreateIncrementalRetryProvider
   • CreateExponentialRetryProvider
   • CreateNoneRetryProvider
3. 將 SqlRetryLogicBaseProvider 物件指派給 RetryLogicProvider 屬性。

注意

偵測可重試的例外狀況是重試模式的重要部分。 套用重試邏輯之前，請務必調查例外狀況，並選擇最適合您案例的重試提供者。 首先，記錄您的例外狀況並尋找暫時性錯誤。

注意

命令 逾 時會在重試邏輯內每次執行命令時重新開機，並在套用重試時間延遲之後重新開機。 這兩個動作之間沒有時間重迭。

注意

除非已在應用程式組態檔中設定，否則不會啟用預設重試邏輯提供者。 如需詳細資訊，請參閱 可設定的重試邏輯組態檔。

注意

的 CommandBehavior.CloseConnection 命令與內建重試邏輯不相容。 第一次執行嘗試之後，基礎連接會立即關閉，且無法再用於後續的重試。

範例

下列範例會建立資料庫，並建立其作用中的連線。 當資料庫具有作用中的連接時，它會嘗試以新的 SqlConnection 和 SqlCommand 使用 的 卸載 SqlRetryLogicBaseProvider 它。 您應該透過資料庫終止使用中的連線，以在超過重試次數之前解除封鎖第二個命令。
封鎖連線會模擬一種情況，例如命令仍在資料庫中執行，而且不太可能完成。

/// Detecting retriable exceptions is a vital part of the retry pattern.
/// Before applying retry logic it is important to investigate exceptions and choose a retry provider that best fits your scenario.
/// First, log your exceptions and find transient faults.
/// The purpose of this sample is to illustrate how to use this feature and the condition might not be realistic.

    private const string DefaultDB = "Northwind";
    private const string CnnStringFormat = "Server=localhost; Initial Catalog={0}; Integrated Security=true; pooling=false;";
    private const string DropDatabaseFormat = "DROP DATABASE {0}";
    private const string CreateDatabaseFormat = "CREATE DATABASE {0}";

    // For general use
    private static SqlConnection s_generalConnection = new SqlConnection(string.Format(CnnStringFormat, DefaultDB));

    static void Main(string[] args)
    {
        // 1. Define the retry logic parameters
        var options = new SqlRetryLogicOption()
        {
            NumberOfTries = 5,
            MaxTimeInterval = TimeSpan.FromSeconds(20),
            DeltaTime = TimeSpan.FromSeconds(1),
            AuthorizedSqlCondition = null,
            // error number 3702 : Cannot drop database "xxx" because it is currently in use.
            TransientErrors = new int[] {3702}
        };

        // 2. Create a retry provider
        var provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options);

        // define the retrying event to report execution attempts
        provider.Retrying += (object s, SqlRetryingEventArgs e) =>
            {
                int attempts = e.RetryCount + 1;
                Console.ForegroundColor = ConsoleColor.Yellow;
                Console.WriteLine($"attempt {attempts} - current delay time:{e.Delay} \n");
                Console.ForegroundColor = ConsoleColor.DarkGray;
                if (e.Exceptions[e.Exceptions.Count - 1] is SqlException ex)
                {
                    Console.WriteLine($"{ex.Number}-{ex.Message}\n");
                }
                else
                {
                    Console.WriteLine($"{e.Exceptions[e.Exceptions.Count - 1].Message}\n");
                }

                // It is not good practice to do time-consuming tasks inside the retrying event which blocks the running task.
                // Use parallel programming patterns to mitigate it.
                if (e.RetryCount == provider.RetryLogic.NumberOfTries - 1)
                {
                    Console.WriteLine("This is the last chance to execute the command before throwing the exception.");
                    Console.WriteLine("Press Enter when you're ready:");
                    Console.ReadLine();
                    Console.WriteLine("continue ...");
                }
            };

        // Open a general connection.
        s_generalConnection.Open();

        try
        {
            // Assume the database is creating and other services are going to connect to it.
            RetryCommand(provider);
        }
        catch
        {
            s_generalConnection.Close();
            // exception is thrown if connecting to the database isn't successful.
            throw;
        }
        s_generalConnection.Close();
    }

    private static void ExecuteCommand(SqlConnection cn, string command)
    {
        using var cmd = cn.CreateCommand();
        cmd.CommandText = command;
        cmd.ExecuteNonQuery();
    }

    private static void FindActiveSessions(SqlConnection cnn, string dbName)
    {
        using var cmd = cnn.CreateCommand();
        cmd.CommandText = "DECLARE @query NVARCHAR(max) = '';" + Environment.NewLine +
            $"SELECT @query = @query + 'KILL ' + CAST(spid as varchar(50)) + ';' FROM sys.sysprocesses WHERE dbid = DB_ID('{dbName}')" + Environment.NewLine +
            "SELECT @query AS Active_sessions;";
        var reader = cmd.ExecuteReader();
        if (reader.Read())
        {
            Console.ForegroundColor = ConsoleColor.Green;
            Console.Write($">> Execute the '{reader.GetString(0)}' command in SQL Server to unblock the running task.");
            Console.ResetColor();
        }
        reader.Close();
    }

如何搭配同步命令使用

private static void RetryCommand(SqlRetryLogicBaseProvider provider)
{
    // Change this if you already have a database with the same name in your database.
    string dbName = "RetryCommand_TestDatabase";

    // Subscribe a new event on retry event and discover the active sessions on a database
    EventHandler<SqlRetryingEventArgs> retryEvent = (object s, SqlRetryingEventArgs e) =>
    {
        // Run just at first execution
        if (e.RetryCount == 1)
        {
            FindActiveSessions(s_generalConnection, dbName);
            Console.WriteLine($"Before exceeding {provider.RetryLogic.NumberOfTries} attempts.");
        }
    };

    provider.Retrying += retryEvent;

    // Create a new database.
    ExecuteCommand(s_generalConnection, string.Format(CreateDatabaseFormat, dbName));
    Console.WriteLine($"The '{dbName}' database is created.");

    // Open a connection to the newly created database to block it from being dropped.
    using var blockingCnn = new SqlConnection(string.Format(CnnStringFormat, dbName));
    blockingCnn.Open();
    Console.WriteLine($"Established a connection to '{dbName}' to block it from being dropped.");

    Console.WriteLine($"Dropping `{dbName}`...");
    // Try to drop the new database.
    RetryCommandSync(provider, dbName);

    Console.WriteLine("Command executed successfully.");

    provider.Retrying -= retryEvent;
}

private static void RetryCommandSync(SqlRetryLogicBaseProvider provider, string dbName)
{
    using var cmd = s_generalConnection.CreateCommand();
    cmd.CommandText = string.Format(DropDatabaseFormat, dbName);
    // 3. Assign the `provider` to the command
    cmd.RetryLogicProvider = provider;
    Console.WriteLine("The first attempt, before getting into the retry logic.");
    cmd.ExecuteNonQuery();
}

如何搭配 asynchoronous 命令使用

private static void RetryCommand(SqlRetryLogicBaseProvider provider)
{
    // Change this if you already have a database with the same name in your database.
    string dbName = "RetryCommand_TestDatabase";

    // Subscribe to the retry event and discover active sessions in a database
    EventHandler<SqlRetryingEventArgs> retryEvent = (object s, SqlRetryingEventArgs e) =>
    {
        // Run just at first execution
        if (e.RetryCount == 1)
        {
            FindActiveSessions(s_generalConnection, dbName);
            Console.WriteLine($"Before exceeding {provider.RetryLogic.NumberOfTries} attempts.");
        }
    };

    provider.Retrying += retryEvent;

    // Create a new database.
    ExecuteCommand(s_generalConnection, string.Format(CreateDatabaseFormat, dbName));
    Console.WriteLine($"The '{dbName}' database is created.");

    // Open a connection to the newly created database to block it from being dropped.
    using var blockingCnn = new SqlConnection(string.Format(CnnStringFormat, dbName));
    blockingCnn.Open();
    Console.WriteLine($"Established a connection to '{dbName}' to block it from being dropped.");

    Console.WriteLine("Dropping the database...");
    // Try to drop the new database.
    RetryCommandAsync(provider, dbName).Wait();

    Console.WriteLine("Command executed successfully.");

    provider.Retrying -= retryEvent;
}

private static async Task RetryCommandAsync(SqlRetryLogicBaseProvider provider, string dbName)
{
    using var cmd = s_generalConnection.CreateCommand();
    cmd.CommandText = string.Format(DropDatabaseFormat, dbName);
    // 3. Assign the `provider` to the command
    cmd.RetryLogicProvider = provider;
    Console.WriteLine("The first attempt, before getting into the retry logic.");
    await cmd.ExecuteNonQueryAsync();
}

如何搭配舊版非同步命令使用

除了將提供者指派給命令並執行命令之外，也可以使用下列 SqlRetryLogicBaseProvider 方法直接執行它：

• Execute<TResult>(Object, Func<TResult>)
• ExecuteAsync(Object, Func<Task>, CancellationToken)
• ExecuteAsync<TResult>(Object, Func<Task<TResult>>, CancellationToken)

private static void RetryCommand(SqlRetryLogicBaseProvider provider)
{
    // Change this if you already have a database with the same name in your database.
    string dbName = "RetryCommand_TestDatabase";

    // Subscribe to the retry event and discover the active sessions in a database
    EventHandler<SqlRetryingEventArgs> retryEvent = (object s, SqlRetryingEventArgs e) =>
    {
        // Run just at first execution
        if (e.RetryCount == 1)
        {
            FindActiveSessions(s_generalConnection, dbName);
            Console.WriteLine($"Before exceeding {provider.RetryLogic.NumberOfTries} attempts.");
        }
    };

    provider.Retrying += retryEvent;

    // Create a new database.
    ExecuteCommand(s_generalConnection, string.Format(CreateDatabaseFormat, dbName));
    Console.WriteLine($"The '{dbName}' database is created.");

    // Open a connection to the newly created database to block it from being dropped.
    using var blockingCnn = new SqlConnection(string.Format(CnnStringFormat, dbName));
    blockingCnn.Open();
    Console.WriteLine($"Established a connection to '{dbName}' to block it from being dropped.");

    Console.WriteLine("Dropping the database...");
    // Try to drop the new database.
    RetryCommandBeginExecuteAsync(provider, dbName).Wait();

    Console.WriteLine("Command executed successfully.");

    provider.Retrying -= retryEvent;
}

private static async Task RetryCommandBeginExecuteAsync(SqlRetryLogicBaseProvider provider, string dbName)
{
    using var cmd = s_generalConnection.CreateCommand();
    cmd.CommandText = string.Format(DropDatabaseFormat, dbName);
    // Execute the BeginExecuteXXX and EndExecuteXXX functions by using Task.Factory.FromAsync().
    // Apply the retry logic by using the ExecuteAsync function of the configurable retry logic provider.
    Console.WriteLine("The first attempt, before getting into the retry logic.");
    await provider.ExecuteAsync(cmd, () => Task.Factory.FromAsync(cmd.BeginExecuteNonQuery(), cmd.EndExecuteNonQuery));
}

注意

非同步程式設計模型 (APM) 是一種舊版模式，使用開頭為 Begin 和 End 的一對方法，以及稱為 的 IAsyncResult 介面。 不建議在新應用程式中使用此模式。 這些方法適用于回溯相容性。