Handling Database Reconnection Issues in .NET with Polly

What’s the Issue?

The issue in this case can be define as the scenario that occurs when the database connection is temporarily lost and then restored after 30 seconds. To simulate this issue, we can manually stopped the SQL Server service for 30 seconds and then restart it. 

To demo the functionality of Polly library in connection retry, we will create a Student class and Student table, and a page to add value to this table in DB.

public class Student
{
    [Key]
    public int Id { get; set; }
    public string ?Name { get; set; }
    public string ?Course { get; set; }
}

The code implemented to insert an instance of Student entry is very simple and straightforward.

public async Task<IActionResult> OnPostAsync()
 {
     if (!ModelState.IsValid)
     {
         return Page();
     }
     _context.Students.Add(Student);
     await _context.SaveChangesAsync();//save to DB
     return RedirectToPage("./PollyDemo"); // Redirect to the same page to show the updated list
 }

When the application encounters a database disconnection, it returns an ‘Operation timed out’ error, even if the service is restarted after 30 seconds.

This issue can be mitigated by using a dotnet library known as Polly.

Polly is a .NET resilience and transient-fault-handling library that allows developers to express policies such as Retry, Circuit Breaker, Timeout, Bulkhead Isolation, and Fallback in a fluent and thread-safe manner. It is designed to help manage the transient faults that can occur in distributed systems, improving the stability and reliability of applications. Polly is highly extensible, supporting custom policies and strategies, and it integrates seamlessly with asynchronous programming patterns in .NET, making it a powerful tool for building robust and resilient applications.

Resolving the Issue

Let’s start with the installation of this library.

dotnet add package Polly

First, we should create a DatabaseReconnectSettings class, which needs to map to the appsettings values that specify the number of retry attempts and the time interval between each retry if the database connection is lost.

DatabaseReconnectSettings.cs

public class DatabaseReconnectSettings
  {
      public int RetryCount { get; set; }
      public int RetryWaitPeriodInSeconds { get; set; }
  }

Then we will be adding the following to appsettings. We will retry 5 times for every 5 seconds, if the connection of the DB to the web application has been lost.

"DatabaseReconnectSettings": {
    "RetryCount": 5,
    "RetryWaitPeriodInSeconds": 5
  }

Now we will create an interface IDatabaseRetryService and a class DatabaseRetryService that will implement 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);
     }
 }

In the code, retryPolicy is used to define the retry strategy, with configurable values for the number of retries and the interval between retries passed into the policy. fallbackPolicy is designed to handle the situation where reconnecting fails even after the maximum number of retry attempts.

The _retryPolicy field combines the fallback and retry policies, with the fallback policy as the outermost layer. This setup ensures that the retry policy is attempted first, and if all retries fail, the fallback policy is then invoked. The ExecuteWithRetryAsync method will be implemented using these defined retry and fallback mechanisms. Additionally, logging has been incorporated to monitor the application of multiple retry policies and to document instances where connections fail despite the maximum number of retry attempts.

Now, we will register the DatabaseRetryService in the Program.cs file of the .NET application.

builder.Services.AddDbContext<MyDbContext>(options =>
 options.UseSqlServer(builder.Configuration.GetConnectionString("MyDBContext")));
builder.Services.Configure<DatabaseReconnectSettings>(builder.Configuration.GetSection("DatabaseReconnectSettings"));
builder.Services.AddSingleton<IDatabaseRetryService, DatabaseRetryService>();

We will update the OnPostAsync method that saves the instance of a Student

public async Task<IActionResult> OnPostAsync()
  {
      if (!ModelState.IsValid)
      {
          return Page();
      }
      _context.Students.Add(Student);
      await _databaseRetryService.ExecuteWithRetryAsync(async () =>
      {
          await _context.SaveChangesAsync();
      });
      return RedirectToPage("./PollyDemo"); // Redirect to the same page to show the updated list
  }

The ExecuteWithRetryAsync method is utilized during database save operations to ensure that a Polly retry policy is applied in case of a temporary database connection loss.

Conclusion

In conclusion, this article demonstrated how Polly can assist .NET applications in managing temporary database disconnections by retrying failed operations, thereby enhancing the stability and reliability of the applications.