How to Implement Database Connection Resiliency in ASP.NET Core

Nowadays, it is evident that a high-quality application has to be reliable, performant, and even quite scalable. In fact, an application’s reliability usually depends on several important factors. However, one of the most vital ones is surely resiliency. Resiliency is nothing but the ability of an app to withstand faults during run time.
ASP.NET is a widely popular programming language with which professionals can build several web applications. Those include REST APIs, web pages, hubs, microservices, and more. As per Statista, in 2024, almost 25.2 percent of developers prefer using . NET. So, now you know why it is important to know the details regarding database connection resiliency in ASP.NET core.
Hence, let’s check out the details of how to bring resiliency to ASP.NET Core’s database connections with the help of Entity Framework Core.

Steps To Create an ASP.NET Core Web API Project

In Visual Studio 2022, you can successfully create an ASP.NET Core Web API project. To do the task, you can choose professional ASP.NET Core development services, or just perform these steps:

1. Step 1: First, you must launch the Visual Studio 2022 IDE.
2. Step 2: Next, just go to the ‘Create new project’ window. Then, choose the option ‘ASP.NET Core Web API’ from the list of displayed templates.
3. Step 3: After that, click on the option ‘Next.’
4. Step 4: Now, go to the window showing ‘Configure your new project’ and then, specify both the name and location for your new project. Moreover, you have to go to the check box showing ‘Place solution and project in the same directory’, according to your preferences.
5. Step 5: Then, click on the option ‘Next.’
6. Step 6: In this step, you have to go to the window showing ‘Additional Information.’ Then, choose the option ‘.NET 8.0 (Long Term Support)’ as the correct version of the framework. After that, make sure that you have checked the check box showing ‘Use controllers.’ In this particular project, we will use controllers.
7. Step 7: In another place, you can go to the option ‘Additional Information.’ Then, leave the ‘Authentication Type’ set to the default option ‘None.’ Next, make sure that some check boxes are unchecked. Those are ‘Enable Open API Support,’ Configure for HTTPS,’ and ‘Enable Docker.’ Here, you won’t need these features.
8. Step 8: Finally, click on Create.

Now, we have to use this ASP.NET Core Web API project, and work with some code examples below.

How To Develop An Execution Strategy in EF Core

In EF Core, a strategy of execution is usually defined as a certain element. This element can encapsulate the logic for handling the execution errors of database commands. Moreover, it can help in the retrying tasks if the errors look transitory. Only with a correct execution strategy, developers can ensure that their applications are able to recover from transient errors, even without the need for human intervention.
Just by using the right CreateExecutionStrategy method, you can easily create an effective execution strategy. Let’s see the right code below:
var strategy = _context.Database.CreateExecutionStrategy( await strategy.ExecuteAsync(async () => { await using var transaction = await _context.Database.BeginTransactionAsync( //Write your custom code here to perform CRUD operations //against the database await transaction.CommitAsync( }

Now, as you see from the following example, usually an execution strategy is stated in the OnConfiguring method of your class of custom DbContext class.
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder .UseSqlServer( "Server= mssqldb;Database=Test;Trusted_Connection=True", options => options.EnableRetryOnFailure }

If you are working with Azure SQL Database, EF Core has the power to offer the right amount of resiliency and retry logic for your database. However, you must not forget to enable the right EF Core execution strategy for each of those DbContext connection that your application has to make for leveraging the right connection resiliency of EF Core.
So, let’s check out a code that illustrates how to enable resilient SQL connections with the help of EF Core. You can retry this whenever you feel that the database connection is going down.

builder.Services.AddDbContext(options => { options.UseSqlServer(builder.Configuration["IDGConnectionString"], sqlServerOptionsAction: sqlOptions => { sqlOptions.EnableRetryOnFailure( maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(45) } }

How To Use Connection Resiliency With EF Core Transactions

If you have retrying the task in EF Core, every call that you make to the SaveChanges method will be retried. It will be retried as a unit whenever a failure in your database connection takes place. However, you can make sure that every operation inside a transaction is executed just by executing a transaction block in the code of your application.
For this, you just have to use the method called BeginTransaction and have to invoke an execution strategy explicitely with a delegate. Let’s see the right code below:

var strategy = db.Database.CreateExecutionStrategy( strategy.Execute( () => { using var context = new LibraryContext( using var transaction = context.Database.BeginTransaction( context.Books.Add(new Book { Id = 1, Title = "Let us C" } context.SaveChanges( context.Books.Add(new Book { Id = 2, Title = "Mastering C# 8.0" } context.SaveChanges( transaction.Commit( }

How To Create A CustomDbContext class in EF Core?

Usually, you can specify your execution strategy in the mrethod of OnConfiguring of the DbContext. Let’s ser the code that shows that CustomDbContext class that extends the EF Core’s DbContext class. It even implements the methods of OnModelCreating and OnConfiguring.

public class CustomContext : DbContext { public DbSet Customers { get; set; } public CustomContext(DbContextOptions options) : base(options) { } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder .UseSqlServer( "Server= mssqldb;Database=Test;Trusted_Connection=True", options => options.EnableRetryOnFailure } protected override void OnModelCreating(ModelBuilder modelBuilder) { //Write your custom code here to //configure the models used in your application } }

Conclusion

So, these are some methods that will help you to easily handle database connection failures, along with making your application quite resilient. However, to perform the job in the correct way and without facing any issues, you can always hire ASP.NET developers, who are professionals and experienced in this field.