Edit

Configurable retry logic in SqlClient introduction

  • Article

Applies to: .NET Framework .NET .NET Standard

Download ADO.NET

Configurable retry logic lets developers and administrators manage application behavior when transient faults happen. The feature adds controls during connection or execution of a command. The controls can be defined through code or an application configuration file. Transient error numbers and retry properties can be defined to control retry behavior. Also, regular expressions can be used to filter specific SQL statements.

Feature components

This feature consists of three main components:

  1. Core APIs: Developers can use these interfaces to implement their own retry logic on SqlConnection and SqlCommand objects. For more information, see Configurable retry logic core APIs in SqlClient.
  2. Pre-defined configurable retry logic: Built-in retry logic methods using the core APIs are accessible from the SqlConfigurableRetryFactory class. For more information, see Internal retry logic providers in SqlClient.
  3. Configuration file schema: To specify the default retry logic for SqlConnection and SqlCommand in an application. For more information, see Configurable retry logic configuration file with SqlClient.

Quick start

To use this feature, follow these four steps:

  1. Enable the safety switch in the preview version. For information on how to enable the AppContext safety switch, see Enable configurable retry logic.

  2. Define the retry logic options using SqlRetryLogicOption.
    In this sample, some of the retry parameters are set and the rest of them will use the default values.

    // Define the retry logic parameters
var options = new SqlRetryLogicOption()
{
    // Tries 5 times before throwing an exception
    NumberOfTries = 5,
    // Preferred gap time to delay before retry
    DeltaTime = TimeSpan.FromSeconds(1),
    // Maximum gap time for each delay time before retry
    MaxTimeInterval = TimeSpan.FromSeconds(20)
};

  3. Create a retry logic provider using your SqlRetryLogicOption object.

    // Create a retry logic provider
SqlRetryLogicBaseProvider provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options);

  4. Assign the SqlRetryLogicBaseProvider instance to the SqlConnection.RetryLogicProvider or SqlCommand.RetryLogicProvider.
    In this sample, the connection open command will retry if it hits one of the transient errors in the SqlConfigurableRetryFactory internal list for a maximum of five times.

    // Assumes that connection is a valid SqlConnection object 
// Set the retry logic provider on the connection instance
connection.RetryLogicProvider = provider;
// Establishing the connection will retry if a transient failure occurs.
connection.Open();

Note

These steps are the same for a command execution, except you would instead assign the retry provider to the SqlCommand.RetryLogicProvider property before executing the command.

See also