Resolving Database Reconnection Challenges in .NET with Polly

To demo the functionality of Polly library in connection retry, I create aEmployeeclass andEmployeestable, 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 to the Employees Table looks like this.

The code behind is very straightforward, 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
}

Simulating 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

Implementation of database reconnect settings

First, I created aDatabaseReconnectSettingsclass, this has to map to the app settings value about how many times we should retry, and the time interval for each retry if the DB connection has been lost.

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

Add the following to the app settings. I will retry 5 times for every 5 seconds if the connection of the DB to my website has been lost.

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

Implementation of IDatabase retry service interface

Then, create an InterfaceIDatabaseRetryServiceand a classDatabaseRetryServicethat implements 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);
    }
}

Configuration of retry and fallback policies

From the code,retryPolicymeans to set the retry policy, the configurable value of retry and retry interval has been passed into the policy.

fallbackPolicymeans to capture the failed to reconnect after maximum retry effort.

This line

_retryPolicy = Policy.WrapAsync(fallbackPolicy, retryPolicy);

is called policy wrapping. The_retryPolicyfield 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.

MethodExecuteWithRetryAsyncwill 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.

Registration of database retry service

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>();

Integration with save employee page

Now, we move back to our save Employee page, in the methodOnPostAsync()

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
}

TheExecuteWithRetryAsyncmethod 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 ran my web app and stop again the SQL service during the saving process, and restarted 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.

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.