In recent years, the new library Microsoft.Data.SqlClient has emerged. While initially similar to the well-known System.Data.SqlClient, it is now the focus for all future developments.
You can upgrade from System.Data.SqlClient to Microsoft.Data.SqlClient without requiring any code refactoring!
How to implement retry logic?
To implement retry logic, start by installing the NuGet package for the new library. If you have System.Data.SqlClient installed, make sure to uninstall it before proceeding.
dotnet add package Microsoft.Data.SqlClientNow we are ready to dive into the code. The first step is to define an object that specifies how the SqlClient should handle faults:
var retryLogicOptions = new SqlRetryLogicOption
{
NumberOfTries = 5,
DeltaTime = TimeSpan.FromSeconds(2),
MaxTimeInterval = TimeSpan.FromSeconds(50),
RetryLogicProvider = SqlRetryLogicOption.CreateExponentialRetryProvider(),
TransientErrors = new List<int> { 4060, 10928, 10929 }
};Properties:
| NumberOfTries | Defines how many times the query will be retried in case of a failure. |
| DeltaTime | Specifies the delay between retry attempts, in line with the chosen retry strategy. |
| MaxTimeInterval | Defines the maximum allowable time for all retries, ensuring retries remain within a reasonable limit. |
| RetryLogicProvider | Defines the retry logic to use. Options include predefined strategies (e.g., exponential or fixed interval) or a custom implementation. |
| TransientErrors | A list of SQL Server error codes treated as transient, which can be customized for specific use cases. rrors in this list will trigger the retry logic, while those not included will result in an exception. Focusing on transient errors (e.g., a lost database connection) ensures the retry logic is not triggered by non-transient issues like query syntax errors or stored procedure bugs. |
| RetryableMethod | Defines which SQL methods or operations are subject to retry logic. Available options are: Open, Execute, OpenAndExecute, None. Open, for example, applies retry logic to errors encountered when opening a connection. |
We are now ready to execute our query using the retry policy we just configured:
using (var connection = new SqlConnection("ConnectionString"))
{
//Qui specifichiamo la logica di retry
connection.RetryLogicProvider = retryLogicOptions;
try
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM JediMasters";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader[0]);
}
}
}
}
catch (SqlException ex)
{
//Exception
}
}When command.ExecuteReader() is executed and an error occurs, subsequent attempts will follow this logic:
First Attempt: 2 seconds
Second Attempt: 4 seconds
Third Attempt: 8 seconds
Fourth Attempt: 16 seconds
Fifth Attempt: 32 seconds
The total retry duration will be 62 seconds. However, since the MaxTimeInterval property is set to 50 seconds, an exception will be thrown once this limit is reached.
Conclusioni
We have explored how to make our code more resilient using the “by design” retry feature in the new Microsoft.Data.SqlClient library. This functionality allows us to handle query retries efficiently in response to specific errors or states.
In the past, I often managed retry logic using the Polly library, and at times, I implemented custom solutions with while loops. Moving forward, this library will undoubtedly be my go-to choice, as it eliminates the need for external dependencies!
If you found this information helpful, feel free to share it with your colleagues.
Happy coding!
