To demo the functionality of Polly library in connection retry , I create an Employee class and Employeestable, and a page to add value to this table in DB.
public class Employee
{
[Key]
public int Id { get; set; }
public string ?Name { get; set; }
public string ?Designation { get; set; }
}The page to add value into Employees Table look like this
The code behind is very straight forward, just a standard C# EF add code add data to DB.
public async Task<IActionResult> OnPostAsync()
{
if (!ModelState.IsValid)
{
return Page();
}
_context.Employees.Add(Employee);
await _context.SaveChangesAsync();//save to DB
return RedirectToPage("./PollyDemo"); // Redirect to the same page to show the updated list
}To simulate a temporary database disconnection, I manually stopped the SQL Server service for 30 seconds and then restarted it. This action was intended to mimic a scenario where the database connection is temporarily lost and then restored after 30 seconds
When the website encounters a database disconnection, it returns an ‘Operation timed out’ error, even if the service is restarted after 30 seconds.
To address this issue of temporary database connection loss, the Polly library can be utilized effectively
First, I created a DatabaseReconnectSettings class, this have to map to the appsettings value about how many times we should retry, and the time interval for each retry if the DB connection has lost.
public class DatabaseReconnectSettings
{
public int RetryCount { get; set; }
public int RetryWaitPeriodInSeconds { get; set; }
}Add the following to appsettings. I will retry 5 times for every 5 second, if the connection of the DB to my website has lost.
"DatabaseReconnectSettings": {
"RetryCount": 5,
"RetryWaitPeriodInSeconds": 5
}Then, create an Interface IDatabaseRetryService and a class DatabaseRetryService that implemented the interface.
public interface IDatabaseRetryService
{
Task ExecuteWithRetryAsync(Func<Task> action);
} public class DatabaseRetryService : IDatabaseRetryService
{
private readonly IAsyncPolicy _retryPolicy;
private readonly IOptions<DatabaseReconnectSettings> _databaseReconnectSettings;
private readonly string _logFilePath=@"C:\Logs\ReconnectLog.txt";
public DatabaseRetryService(IOptions<DatabaseReconnectSettings> settings)
{
_databaseReconnectSettings = settings;
var retryPolicy = Policy
.Handle<SqlException>()
.WaitAndRetryAsync(
_databaseReconnectSettings.Value.RetryCount,
retryAttempt => TimeSpan.FromSeconds(_databaseReconnectSettings.Value.RetryWaitPeriodInSeconds),
onRetry: (exception, timeSpan, retryCount, context) =>
{
File.AppendAllText(_logFilePath,$"Connection lost, retry attempt {retryCount} at {DateTime.Now} . Exception Message: {exception.Message}" + Environment.NewLine);
});
var fallbackPolicy = Policy
.Handle<SqlException>()
.FallbackAsync(
fallbackAction: cancellationToken => Task.CompletedTask,
onFallbackAsync: async e =>
{
await Task.Run(() => File.AppendAllText(_logFilePath, $"Failed after maximum retries. Exception Message: {e.Message}" + Environment.NewLine));
});
_retryPolicy = Policy.WrapAsync(fallbackPolicy, retryPolicy);
}
public async Task ExecuteWithRetryAsync(Func<Task> action)
{
var context = new Context();
int attempt = 0;
await _retryPolicy.ExecuteAsync(async (ctx) =>
{
attempt++;
await action();
}, context);
File.AppendAllText(_logFilePath, $"Connection successfully reconnected at attempt {attempt} at {DateTime.Now}" + Environment.NewLine);
}
}From the code, retryPolicy means to set the retry policy, the configurable value of retry and retry internal has benn pass into the policy.
fallbackPolicy means to capture the failed to reconnect after maximum retry effort.
This line
_retryPolicy = Policy.WrapAsync(fallbackPolicy, retryPolicy);is called policy wrapping. The _retryPolicy field is a combination (wrap) of the fallback and retry policies, with the fallback policy being the outermost layer. This means the retry policy is attempted first, and if it fails after all attempts, the fallback policy is invoked.
Method ExecuteWithRetryAsync will be implemented with the defined retry and fallback mechanisms in place.
Logging has also been implemented to monitor the enforcement of multiple retry policies and to record instances where connections fail even after the maximum number of attempts.
We also need to register the service to the Program.cs.
builder.Services.AddDbContext<MyDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("MyDBContext")));
builder.Services.Configure<DatabaseReconnectSettings>(builder.Configuration.GetSection("DatabaseReconnectSettings"));
builder.Services.AddSingleton<IDatabaseRetryService, DatabaseRetryService>();Now, we move back to our save Employee page, in the method OnPostAsync()
public async Task<IActionResult> OnPostAsync()
{
if (!ModelState.IsValid)
{
return Page();
}
_context.Employees.Add(Employee);
await _databaseRetryService.ExecuteWithRetryAsync(async () =>
{
await _context.SaveChangesAsync();
});
return RedirectToPage("./PollyDemo"); // Redirect to the same page to show the updated list
}The ExecuteWithRetryAsync method is used during database save operations to ensure that a retry policy from Polly is enforced in the event of a temporary loss of connection to the database.
To test it, I run my web app and stop again the SQL service during saving process, and restart it after 40 seconds. Here is the log file.
As demonstrated, the retry policy was effectively enforced, the first three connection attempts failed, but the connection was successfully established on the fourth attempt.
This is how to log file looks like if failed to connect after 5 attempts.
In conclusion, this article showed how Polly can help .NET applications handle temporary database disconnections by retrying failed operations. This makes applications more stable and reliable.
You can have this code on my Github.
