ASP.NET Core 9: Implementing Distributed Caching using SQL Server

-

ASP.NET Core is one of the best technologies for building modern web apps. Since it is cross-platform and lightweight, it's been used by several applications and products for building applications. Most of these applications are high-traffic and they run on multi-server deployment with load-balanced across these servers. In such environments managing data traffic for reading data from database server(s) with respect to various requests could be a challenge. In the deployment architecture the data availability must be guaranteed. Another important features of ASP.NET Core is that the use of ASP.NET Core in Microservices architectures. In case of Microservices, it is important to part of the application pattern to share data across Microservices using Asynchornous communication across them. 

To handle the high data availability as well as data communication across Microservices implementation in ASP.NET Core applications, we should consider using the Caching feature of ASP.NET Core. In ASP.NET Core application, we can use In-Memory caching as well as Distributed Caching. In-Memory caching uses the Web Server's memory to store the data. The Server has direct access of this data. This data access makes the data access extremely fast. But the in-memory data cache is specific to the server and hence in the multi-server deployed application this approach is not suitable. Whereas in distributed cache the data is cached in the separate server application. The ASP.NET Core can use Redis Cache or SQL Server to store the cached data. I have already published an article on using Redis Cache on this link. In this article, we will be using the SQL Server for caching data. Figure 1, explains the idea behind the SQL Server caching.



Figure 1: SQL Server Distribute Cache

As explained in Figure 1, the ASP.NET Core app is deployed on multiple host servers. The browser client makes the first request. The load balancer sent this request to the Host server 1, which make call to SQL Server database to fetch data. This server has the Cache logic written in ASP.NET Core application that cache the data in SQL Server database. The response with data is then sent by the Server Host 1 to the Load balancer and then back to browser client. The second request from the browser to the Load Balancer is routed to the Server Host 2 (I am assuming that the Server Host 1 is full of requests). The Server Host 2, using its caching logic connect to the SQL Server Cache Server and then read data from SQL Server cache. This data is then sent back to the client. Hence the multi-server deployment of ASP.NET Core application guarantees the data availability with performance optimization.

Installing CLI Tool for SQL Server Cache

To use SQL Server Cache in ASP.NET Core application, we need to instal CLI tool. Open the Command Prompt (or Power Shell Command or Terminal window for Linux/Mac) and run the following command to SQL Cache.

dotnet tool install -g dotnet-sql-cache

We need to create table to store cache in SQL Server, run the following command from Command line to create the table

dotnet sql-cache create "SQL Server Connection String" dbo DataCacheTable

I have used the Database named 'MyAppDb' on the local SQL Server instance with Windows Authentication. The above command in my case is:

dotnet sql-cache create "Data Source=.;Initial Catalog=MyAppDb;Integrated Security=SSPI;TrustServerCertificate=True" dbo DataCacheTable


The table named DataCacheTable will be created as shown in Figure 2.


 

Figure 2: The Cache Table

The table contains cache properties.

The Implementation

Step 1: Visual Studio 2022 and create a new ASP.NET Core API project named Core_APICache_Art targeted to .NET 9. In this project add the package named Microsoft.Extensions.Caching.SqlServer as shown in Figure 3.



Figure 3: The SQL Server Cache Extension.

We will be using SQL Server to access data. In my Local Instance of SQL Server, I have database named MyAppDb. This database contains Department table with columns as:

Create Table Department (

DeptNo int Primary Key,

DeptName varchar(100) Not Null,

Location varchar(100) Not null,

Capacity int Not Null

)

We will be using Entity Framework Core to access the data from Department table for the project. Let's add following packages in the Project:

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Relational
  • Microsoft.EntityFrameworkCore.SqlServer
  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.Tools  

Let's scaffold the DbContext class using the following command on the command prompt.

dotnet ef dbcontext scaffold "Data Source=.;Initial Catalog=MyAppDb;Integrated Security=SSPI;TrustServerCertificate=True" Microsoft.EntityFrameworkCore.SqlServer -o Models -t Department

The above code will generate the MyAbbDbContext and Department classes in the Models folder. Move the connection string from the OnConfiguring() method of the MyAppDbContext class to appsettings.json file as shown in Listign 1.


 "ConnectionStrings": {
        "ApDbConn": "Data Source=.;Initial Catalog=April2025;Integrated Security=SSPI;TrustServerCertificate=True"
    }

Listing 1: The connection string in appsettings.json file   

Step 2: Modify the Program.cs for registering the MyAppDbContext class in Dependency container as well as configuring the SQL Server cache as shown in Listing 2.


builder.Services.AddDbContext<MyAppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("ApDbConn")));
// Define the SQLServer Caching
builder.Services.AddDistributedSqlServerCache(options =>
{
    options.ConnectionString = builder.Configuration.GetConnectionString("ApDbConn");
    options.SchemaName = "dbo";
    options.TableName = "DataCacheTable";
});

Listing 2: The MyAppDbContext registration as well as SQL Server Cache Configuration

Step 3: Add the new Empty API Controller named DepartmentController in the Controllers folder. This controller will be constructor injected using MyAppDbContext and IDistributedCache so that DataAccess and Distributed Cache can be performed. The Listing 3 shows code for the controller.


using System.Text.Json;
using Core_APICache_Art.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Distributed;

namespace Core_APICache_Art.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class DepartmentController : ControllerBase
    {
        private readonly April2025Context _context;
        private readonly IDistributedCache _cache;
        public DepartmentController(April2025Context context,IDistributedCache cache)
        {
            _context = context;
            _cache = cache;
        }
        // GET: api/Department
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Department>>> Get()
        {
            var cacheKey = "departments";
            var cachedDepartments = await _cache.GetStringAsync(cacheKey);
            if (!string.IsNullOrEmpty(cachedDepartments))
            {
                var departments = JsonSerializer.Deserialize<List<Department>>(cachedDepartments);
                var response = new
                {
                    Message = "Data read from cache",
                    Data = departments
                };
                return Ok(response);
            }
            // save to cache
            var departmentsList = await _context.Departments.ToListAsync();
            var options = new DistributedCacheEntryOptions()
            {
                AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5),
                SlidingExpiration = TimeSpan.FromMinutes(2)
            };
            await _cache.SetStringAsync(cacheKey, JsonSerializer.Serialize(departmentsList), options);
            var responseFromDb = new
            {
                Message = "Data read from database",
                Data = departmentsList
            };
            return Ok(responseFromDb);
        }
        // GET: api/Department/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Department>> Get(int id)
        {
            var cacheKey = $"department_{id}";
            var cachedDepartment = await _cache.GetStringAsync(cacheKey);
            if (!string.IsNullOrEmpty(cachedDepartment))
            {
                var dept = JsonSerializer.Deserialize<Department>(cachedDepartment);
                var response = new
                {
                    Message = "Data read from cache",
                    Data = dept
                };
                return Ok(response);
            }
            var department = await _context.Departments.FindAsync(id);
            if (department == null)
            {
                return NotFound();
            }
            // save to cache
            var options = new DistributedCacheEntryOptions()
            {
                AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5),
                SlidingExpiration = TimeSpan.FromMinutes(2)
            };
            await _cache.SetStringAsync(cacheKey, JsonSerializer.Serialize(department), options);
            var responseFromDb = new
            {
                Message = "Data read from database",
                Data = department
            };
            return Ok(responseFromDb);
        }
    }
}

Listing 4: The Controller code

As shown in Listing 4, the DepartmentController class contains tow Get() methods. In both these methods the code check if the data is present in Cache if yes then it will be fetched from the cache else it will be read from the database add then it will be cached. Run the application and using .http file make call to Get() methods. Once the data added it will be shown in the DataCacheTable table as shown in Figure 3.



Figure 3: The Data Cached

Once the data is cached and it will be fetched from the SQL Server cache. 

 The code for this article can be downloaded from this link.

Conclusion: The SQL Server Caching in SQL Server is the best choice when the ASP.NET Core application is deployed on multiple servers. The distributed cache in SQL Server makes it easy to have data available across multiple servers so that each request to each server can be processed by data reading from SQL Server Cache.     

            

           

Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

-
Image
Recently, while conducting a session on ASP.NET Core 6, my client raised a query regarding uploading excel files to the ASP.NET Core MVC application and then further saving data from excel to the SQL Server Database table. I gave him the solution and then thought to write a post on it. As we all know, the Excel Workbook is one of the most heavily used files to save data. But to persist data it is recommended that it should be stored in an SQL Server database.  If the data is stored in an SQL Server database it can be queried easily.  Figure 1, explains an implementation of the application which we are going to discuss in the article. Figure 1: The ASP.NET Core That accepts Excel files and then further store data from Excel Files in SQL Server Database The   ExcelDataReader.DataSet Library To read excel files in ASP.NET Core applications, we need to use the ExcelDataReader.DataSet package. This is a lightweight and fast library for reading Excel files in .NET applications....
Read more

ASP.NET Core 6: Downloading Files from the Server

-
Image
In this article, we will implement the file downloading feature using ASP.NET Core 6 application. In real-world applications, we can have various files like reports, spreadsheets, images, PDF documents stored on the server. (Note that all cloud-hosted applications prefer to store these files on the storage services, e.g. Blob, S3, etc.). But what if the client prefers to store files on the separate file server mapped to the web application host server? Then, in that case, we may need to download these files from the file server. In this article, we will implement exactly the same thing.   Step 1: Open Visual Studio 2022 and create a new ASP.NET Core MVC Application targetted to .NET 6. Name this application as Core6_FileDownload. In this project add a new folder and name it as ServerFiles. In this file add some images. (You can add Excel, PDF, Word, Files). Step 2: Since we need to read the ServerFiles folder using the ASP.NET Core application, we need to add the IFi...
Read more

ASP.NET Core 6: Using Entity Framework Core with Oracle Database with Code-First Approach

-
Image
In this article, we will see how to use Oracle Database with Entity Framework Core with Code-First Approach. The reason behind writing a post on this is, recently while conducting training, one of my students raised the query regarding the EF Core with Oracle. Since Oracle is one of the most powerful databases, various enterprise applications are using it for the past several years and since the .NET Core is the most popular technology for building modern apps, the scenarios for accessing the Oracle database in .NET Core apps the use Entity Framework Core is common. Oracle Data Provider for .NET Entity Framework Core The Oracle Data Provider for .NET i.e. ODP.NET is used to access data from the Oracle database in the .NET Core apps using Entity Framework Core. This uses the Oracle.EntityFrameworkCore.dll, a managed library. This data provider allows using Code-First and Database-First approaches to perform Read/Write Operations with Oracle database.    Implementation To implem...
Read more