Azure Redis Cache With Azure SQL In Blazor Project

In this article we will discuss output caching using Azure Redis Cache with Azure SQL database in Blazor projects and create an Indian post office application.

Introduction

In this post, we will discuss output caching using Azure Redis Cache with Azure SQL database in Blazor projects. We will create an Indian Post Office application using which we can get the state-wise post office details.

What is Caching?
 
In computing, cache is a hardware or software component that stores the data so that the future requests for that data can be served faster; the data stored in a cache might be the result of an earlier computation or a copy of the data stored elsewhere. Cache hits are served by reading data from the cache, which is faster than recomputing a result or reading from a slower data store. Thus, more requests can be served from the cache and the system performs fast. A cache hit occurs when the requested data can be found in a cache, while a cache miss occurs when it cannot be found.
 
Azure Redis Cache
 
Azure Redis Cache With Azure SQL In Blazor Project 

Azure Redis Cache is based on the popular open source Redis Cache. Users get the best of both worlds, the rich Redis feature set and ecosystem, and reliable hosting and monitoring from Microsoft. It gives users access to a secure, dedicated Redis Cache, managed by Microsoft.

Microsoft Azure Redis Cache is available in the following tiers,
  • Basic – Single node, multiple sizes, ideal for development/test and non-critical workloads. The basic tier has no SLA.
  • Standard – A replicated cache in a two-node Primary/Secondary configuration managed by Microsoft, with a high availability SLA.
  • Premium - The new Premium tier includes all the Standard-tier features and more, such as better performance compared to Basic or Standard-tier Caches, bigger workloads, data persistence, and enhanced network security.

Blazor framework

Blazor is still an experimental .NET web framework from Microsoft using C#/Razor and HTML that runs in the browser with Web Assembly. Blazor provides all the benefits of a client-side web UI framework using .NET on the client and optionally on the server.

 
Click “Create” button to start the next steps. You can give a valid name to Redis Cache. Please choose an appropriate pricing plan.
 
Azure Redis Cache With Azure SQL In Blazor Project 
 
As I mentioned earlier in this post, there are 3 types of pricing tiers available in Azure. I have chosen “Basic C0” plan. This is the lowest plan and we can use it for our testing purposes. We can choose a better plan for the production stage. You can see the pricing details of various plans by clicking the “View full pricing details” link. In “Basic C0” plan I will get only 250 MB cache. This is enough for our testing purposes.
 
Azure Redis Cache With Azure SQL In Blazor Project 
 
Please copy the connection string from the “Access Keys” section and keep in a secure place. We will use this key later in our Blazor project.
 
Azure Redis Cache With Azure SQL In Blazor Project
 
Create Blazor project in Visual Studio 2017
 
We can create a new Blazor project using Visual Studio 2017 (I am using free community edition). Currently, there are three types of templates available for Blazor. We can choose Blazor (ASP.NET Core hosted) template.
 
Azure Redis Cache With Azure SQL In Blazor Project 
 
Our solution will be ready shortly. Please note that there are three projects created in our solution - “Client”, “Server”, and “Shared”.
 
Azure Redis Cache With Azure SQL In Blazor Project 

By default, Blazor created many files in these three projects. We can remove all the unwanted files like “Counter.cshtml”, “FetchData.cshtml”, “SurveyPrompt.cshtml” from Client project and “SampleDataController.cs” file from Server project and remove “WeatherForecast.cs” file from shared project too.

Create a “Models” folder in the “Shared project. We will create three classes inside this folder.
 
Before that, we must install “Newtonsoft.Json” NuGet package in the shared project. We will use “JsonProperty” attribute to change the property names in some classes. This will be used for JSON serialization.
 
Azure Redis Cache With Azure SQL In Blazor Project 
 
We can create a class file “Classes” and create three classes inside this file.
 
Classes.cs
  1. using Newtonsoft.Json;  
  2. using System.Collections.Generic;  
  3.   
  4. namespace BlazorRedisCache.Shared.Models  
  5. {  
  6.     public class IndiaPO  
  7.     {  
  8.         [JsonProperty(PropertyName = "id")]  
  9.         public long Id { getset; }  
  10.         [JsonProperty(PropertyName = "officeName")]  
  11.         public string OfficeName { getset; }  
  12.         [JsonProperty(PropertyName = "pinCode")]  
  13.         public string PinCode { getset; }  
  14.         [JsonProperty(PropertyName = "taluk")]  
  15.         public string Taluk { getset; }  
  16.         [JsonProperty(PropertyName = "districtName")]  
  17.         public string DistrictName { getset; }  
  18.         [JsonProperty(PropertyName = "stateName")]  
  19.         public string StateName { getset; }  
  20.         [JsonProperty(PropertyName = "telephone")]  
  21.         public string Telephone { getset; }  
  22.     }  
  23.   
  24.     public class PODetails  
  25.     {  
  26.         public double TimeTaken { getset; }  
  27.         public IEnumerable<IndiaPO> IndiaPOs { getset; }  
  28.         public long RecordCount { getset; }  
  29.     }  
  30.   
  31.     public class State  
  32.     {  
  33.         [JsonProperty(PropertyName = "id")]  
  34.         public int Id { getset; }  
  35.         [JsonProperty(PropertyName = "stateName")]  
  36.         public string StateName { getset; }  
  37.     }  
  38. }  

We have created “IndiaPO”, “PODetails” and “State” classes inside this file.

The IndiaPO class will be used for getting post office details and PODetails class will hold the post office details along with the time taken (in seconds) to fetch the data information and total post office count. State class will be used to get the state names from SQL database.

We can add “appsettings.json” file in “Server” project. We will keep Azure SQL and Redis Cache connection strings in this file.
 
appsettings.json
  1. {  
  2.   "MyConfigurations": {  
  3.     "SqlConnection""Server=tcp:sarathsqlserver.database.windows.net,1433;Initial Catalog=sarathsqldb;Persist Security Info=False;
  4. User ID=sarathlal;Password={Your Password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;",  
  5.     "RedisKey""sarathlal.redis.cache.windows.net:6380,password={Your Redis Key Password},ssl=True,abortConnect=False"  
  6.   }  
  7. }  

We must modify the “Starup.cs” class to read Configuration values from appsettings.json file.

Starup.cs
  1. using BlazorRedisCache.Server.DataAccess;  
  2. using Microsoft.AspNetCore.Blazor.Server;  
  3. using Microsoft.AspNetCore.Builder;  
  4. using Microsoft.AspNetCore.Hosting;  
  5. using Microsoft.AspNetCore.ResponseCompression;  
  6. using Microsoft.EntityFrameworkCore;  
  7. using Microsoft.Extensions.Configuration;  
  8. using Microsoft.Extensions.DependencyInjection;  
  9. using Newtonsoft.Json.Serialization;  
  10. using System.Linq;  
  11. using System.Net.Mime;  
  12.   
  13. namespace BlazorRedisCache.Server  
  14. {  
  15.     public class Startup  
  16.     {  
  17.         public Startup(IHostingEnvironment env)  
  18.         {  
  19.             var builder = new ConfigurationBuilder()  
  20.                 .SetBasePath(env.ContentRootPath)  
  21.                 .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);  
  22.   
  23.             Configuration = builder.Build();  
  24.         }  
  25.   
  26.         public IConfigurationRoot Configuration { getset; }  
  27.   
  28.         public void ConfigureServices(IServiceCollection services)  
  29.         {  
  30.             services.AddMvc();  
  31.   
  32.             services.AddResponseCompression(options =>  
  33.             {  
  34.                 options.MimeTypes = ResponseCompressionDefaults.MimeTypes.Concat(new[]  
  35.                 {  
  36.                     MediaTypeNames.Application.Octet,  
  37.                     WasmMediaTypeNames.Application.Wasm,  
  38.                 });  
  39.             });  
  40.   
  41.             var sqlConnectionString = Configuration.GetSection("MyConfigurations").GetSection("SqlConnection").Value;  
  42.   
  43.             services.AddDbContext<MsSqlServerContext>(options =>  
  44.                 options.UseSqlServer(sqlConnectionString)  
  45.             );  
  46.   
  47.             services.AddScoped<IDataAccessProvider, DataAccessProvider>();  
  48.         }  
  49.   
  50.         public void Configure(IApplicationBuilder app, IHostingEnvironment env)  
  51.         {  
  52.             app.UseResponseCompression();  
  53.   
  54.             if (env.IsDevelopment())  
  55.             {  
  56.                 app.UseDeveloperExceptionPage();  
  57.             }  
  58.   
  59.             app.UseMvc(routes =>  
  60.             {  
  61.                 routes.MapRoute(name: "default", template: "{controller}/{action}/{id?}");  
  62.             });  
  63.   
  64.             app.UseBlazor<Client.Program>();  
  65.         }  
  66.     }  
  67. }  

Please note, we have injected “MsSqlServerContextclass inside this class. This class file will be used for database connection using entity framework. Also note that we have injectedDataAccessProvider” class with “IDataAccessProvider” interface. These files will be used to perform CRUD actions in Web API controller. In this post, we will not cover all CRUD actions. We will use only READ method.

We can create a “DataAccess” folder and create above files inside this folder.
 
Create “MsSqlServerContext” class inside “DataAccess” folder.
 
MsSqlServerContext.cs
  1. using BlazorRedisCache.Shared.Models;  
  2. using Microsoft.EntityFrameworkCore;  
  3.   
  4. namespace BlazorRedisCache.Server.DataAccess  
  5. {  
  6.     public class MsSqlServerContext : DbContext  
  7.     {  
  8.         public MsSqlServerContext(DbContextOptions<MsSqlServerContext> options) : base(options)  
  9.         { }  
  10.   
  11.         public DbSet<IndiaPO> IndiaPO { getset; }  
  12.         public DbSet<State> POStates { getset; }  
  13.     }  
  14. }  

We have added two DbSet properties inside this class. These properties will be used for getting data from SQL database.

Create “IDataAccessProvider” interface and add below properties.
 
IDataAccessProvider.cs
  1. using BlazorRedisCache.Shared.Models;  
  2. using System.Collections.Generic;  
  3.   
  4. namespace BlazorRedisCache.Server.DataAccess  
  5. {  
  6.     public interface IDataAccessProvider  
  7.     {  
  8.         PODetails GetIndiaPOs(string stateName);  
  9.         IEnumerable<State> GetStateNames();  
  10.     }  
  11. }  

We can create “DataAccessProvider” class and implement above interface.

We will implement the logic for Redis Cache in this class. We must install “Microsoft.Extensions.Caching.Redis.Core” NuGet package in “Server” project.
 
Azure Redis Cache With Azure SQL In Blazor Project 

Add below code to “DataAccessProvider” class

DataAccessProvider.cs
  1. using BlazorRedisCache.Shared.Models;  
  2. using Microsoft.Extensions.Configuration;  
  3. using Newtonsoft.Json;  
  4. using StackExchange.Redis;  
  5. using System;  
  6. using System.Collections.Generic;  
  7. using System.Linq;  
  8. using System.Threading.Tasks;  
  9.   
  10. namespace BlazorRedisCache.Server.DataAccess  
  11. {  
  12.     public class DataAccessProvider : IDataAccessProvider  
  13.     {  
  14.         private readonly MsSqlServerContext _context;  
  15.         private IConfiguration _configuration;  
  16.   
  17.         public DataAccessProvider(MsSqlServerContext context, IConfiguration Configuration)  
  18.         {  
  19.             _context = context;  
  20.             _configuration = Configuration;  
  21.         }  
  22.   
  23.         public IEnumerable<State> GetStateNames()  
  24.         {  
  25.             return _context.POStates.OrderBy(o => o.StateName).ToList();  
  26.         }  
  27.   
  28.         public PODetails GetIndiaPOs(string stateName)  
  29.         {  
  30.             var redisConnectionString = _configuration.GetSection("MyConfigurations").GetSection("RedisKey").Value;  
  31.             PODetails pODetails = new PODetails();  
  32.             var listPOs = new List<IndiaPO>();  
  33.   
  34.             var redisConnect = ConnectionMultiplexer.Connect(redisConnectionString);  
  35.   
  36.             IDatabase Rediscache = redisConnect.GetDatabase();  
  37.   
  38.             var redisValue = Rediscache.StringGetAsync("stateDetails-" + stateName);  
  39.   
  40.             if (string.IsNullOrEmpty(redisValue.Result))  
  41.             {  
  42.                 listPOs = _context.IndiaPO.Where(w => w.StateName == stateName).ToList();  
  43.                 Rediscache.StringSetAsync("stateDetails-" + stateName, JsonConvert.SerializeObject(listPOs), TimeSpan.FromMinutes(5));  
  44.             }  
  45.             else  
  46.             {  
  47.                 listPOs = JsonConvert.DeserializeObject<List<IndiaPO>>(redisValue.Result);  
  48.             }  
  49.             pODetails.RecordCount = listPOs.Count();  
  50.             pODetails.IndiaPOs = listPOs.Take(100);  
  51.             return pODetails;  
  52.         }  
  53.     }  
  54. }  
Azure Redis Cache With Azure SQL In Blazor Project 

We have defined two methods in this class. “GetStateNames” method will be used to get state names from the database and return as a list.

Logic of Caching mechanism

In “GetIndiaPOs” method, we have established a connection to Azure Redis Cache using “ConnectionMultiplexer.Connect” method and read the value from Cache. As we know, the first time there will be no value available in the cache. So, it will return a null value and automatically get the value from database and store in a list variable. We also set the value to Redis Cache along with a key. Key will be “stateDetails-” + statename. We will store separate cache values for each state user selects. We have also set an expiry time of 5 minutes for each cache. After 5 minutes, the cache will be automatically expired.

If the user chooses the same state name and executes the method within the expiry time, the cache value will be returned. This time, the system will not get the value from the database; instead, it will use the cache value.

Create “POsController” API controller and implement methods.

POsController.cs
  1. using BlazorRedisCache.Server.DataAccess;  
  2. using BlazorRedisCache.Shared.Models;  
  3. using Microsoft.AspNetCore.Mvc;  
  4. using System.Collections.Generic;  
  5. using System.Diagnostics;  
  6.   
  7. namespace BlazorRedisCache.Server.Controllers  
  8. {  
  9.     [Route("api/[controller]")]  
  10.     public class POsController : Controller  
  11.     {  
  12.         private readonly IDataAccessProvider _dataAccessProvider;  
  13.   
  14.         public POsController(IDataAccessProvider dataAccessProvider)  
  15.         {  
  16.             _dataAccessProvider = dataAccessProvider;  
  17.         }  
  18.   
  19.         [HttpGet]  
  20.         public IEnumerable<State> GetStates()  
  21.         {  
  22.             return _dataAccessProvider.GetStateNames();  
  23.         }  
  24.   
  25.         [HttpGet("{stateName}")]  
  26.         public PODetails Get(string stateName)  
  27.         {  
  28.             Stopwatch clock = Stopwatch.StartNew();  
  29.             var data = _dataAccessProvider.GetIndiaPOs(stateName);  
  30.             clock.Stop();  
  31.   
  32.             data.TimeTaken = clock.Elapsed.TotalSeconds;  
  33.             return data;  
  34.   
  35.         }  
  36.   
  37.     }  
  38. }  

We have implemented the methods for getting State names and Post office details. Please note, I have added a stopwatch to calculate the time between request start and end so that we can find the time difference between SQL server request and Cache.

We can now go to “Client” project and add a “GetPOs.cshtml” razor view file.
 
Please add the below code to this file.
 
GetPOs.cshtml
  1. @using BlazorRedisCache.Shared.Models  
  2.   
  3. @page "/getpos"  
  4.   
  5. @inject HttpClient Http  
  6.   
  7. <h4>Azure Redis Cache Example (Indian Pincode App) with Azure SQL and Blazor</h4>  
  8.   
  9. <div class="row" style="padding-top:10px">  
  10.     <div class="col-md-4">  
  11.         <label for="Choose State" class="control-label">Choose State</label>  
  12.     </div>  
  13.   
  14. </div>  
  15. <div class="row" style="padding-top:10px">  
  16.     <div class="col-md-4">  
  17.         <select class="form-control" onchange="@StateChanged">  
  18.             <option value="select">--Select a State--</option>  
  19.             @foreach (var state in states)  
  20.             {  
  21.                 <option value="@state.StateName">@state.StateName</option>  
  22.             }  
  23.         </select>  
  24.     </div>  
  25.     <div class="col-md-4">  
  26.         <input type="button" class="btn btn-default" onclick="@(async () => await GetPO())" value="Get PO Details" />  
  27.     </div>  
  28. </div>  
  29.   
  30. @if (states == null || states.Count == 0)  
  31. {  
  32.     <p><em>Loading...</em></p>  
  33. }  
  34. else  
  35. {  
  36.     if (buttonSelected)  
  37.     {  
  38.         <p><em>Loading...</em></p>  
  39.     }  
  40.     if (pODetails != null)  
  41.     {  
  42.         <div class="row" style="padding-top:10px">  
  43.             <p>Total <b>@pODetails.RecordCount</b> Pincodes fetched in <b>@pODetails.TimeTaken</b> Seconds!</p>  
  44.         </div>  
  45.         counter = 0;  
  46.         <div class="row" style="padding-top:5px">  
  47.             <table class="table table-striped">  
  48.                 <thead>  
  49.                     <tr>  
  50.                         <th>Sl.No</th>  
  51.                         <th>Office Name</th>  
  52.                         <th>Pin Code</th>  
  53.                         <th>Taluk</th>  
  54.                         <th>District Name</th>  
  55.                         <th>Telephone</th>  
  56.                     </tr>  
  57.                 </thead>  
  58.                 <tbody>  
  59.                     @foreach (var po in pODetails.IndiaPOs)  
  60.                     {  
  61.                         counter++;  
  62.                         <tr>  
  63.                             <td>@counter</td>  
  64.                             <td>@po.OfficeName</td>  
  65.                             <td>@po.PinCode</td>  
  66.                             <td>@po.Taluk</td>  
  67.                             <td>@po.DistrictName</td>  
  68.                             <td>@po.Telephone</td>  
  69.                         </tr>  
  70.                     }  
  71.                 </tbody>  
  72.             </table>  
  73.         </div>  
  74.         if (pODetails.RecordCount > 100)  
  75.         {  
  76.             <div class="row" style="padding-top:5px">  
  77.                 <p>We are listing 100 rows only!</p>  
  78.             </div>  
  79.         }  
  80.     }  
  81. }  
  82.   
  83. @functions{  
  84.   
  85.     List<State> states = new List<State>();  
  86.     string stateName;  
  87.     PODetails pODetails;  
  88.   
  89.     int counter;  
  90.     bool buttonSelected;  
  91.   
  92.     protected override async Task OnInitAsync()  
  93.     {  
  94.         buttonSelected = false;  
  95.         states = await Http.GetJsonAsync<List<State>>("api/pos");  
  96.     }  
  97.   
  98.     protected async Task GetPO()  
  99.     {  
  100.         pODetails = null;  
  101.         buttonSelected = true;  
  102.         pODetails = await Http.GetJsonAsync<PODetails>("api/pos/" + stateName);  
  103.         buttonSelected = false;  
  104.     }  
  105.   
  106.     void StateChanged(UIChangeEventArgs stateEvent)  
  107.     {  
  108.         stateName = stateEvent.Value.ToString();  
  109.     }  
  110. }  

We have called the API method for getting state names in “OnInitAsync event and we have added a new method to call API method for getting post office details.

Modify the “NavMenu.cshtml” file in “Shared” folder with below code. This file will be used to navigate the menu.
 
NavMenu.cshtml
  1. <div class="top-row pl-4 navbar navbar-dark">  
  2.     <a class="navbar-brand" href="">Azure Redis Cache in Blazor</a>  
  3.     <button class="navbar-toggler" onclick=@ToggleNavMenu>  
  4.         <span class="navbar-toggler-icon"></span>  
  5.     </button>  
  6. </div>  
  7.   
  8. <div class=@(collapseNavMenu ? "collapse" : null) onclick=@ToggleNavMenu>  
  9.     <ul class="nav flex-column">  
  10.         <li class="nav-item px-3">  
  11.             <NavLink class="nav-link" href="" Match=NavLinkMatch.All>  
  12.                 <span class="oi oi-home" aria-hidden="true"></span> Home  
  13.             </NavLink>  
  14.         </li>  
  15.         <li class="nav-item px-3">  
  16.             <NavLink class="nav-link" href="/getpos">  
  17.                 <span class="oi oi-list-rich" aria-hidden="true"></span> Get Pincode Details  
  18.             </NavLink>  
  19.         </li>  
  20.     </ul>  
  21. </div>  
  22.   
  23. @functions {  
  24. bool collapseNavMenu = true;  
  25.   
  26. void ToggleNavMenu()  
  27. {  
  28.     collapseNavMenu = !collapseNavMenu;  
  29. }  
  30. }  

Modify the “Index.cshtml” file in “Pages” folder too.

Index.cshtml
  1. @page "/"  
  2.   
  3. <h3>Azure Redis Cache with Azure SQL and Blazor</h3>  
  4. <hr />  
  5. <p>  
  6.     We will see the caching with Azure Redis Cache and Azure SQL database in Blazor project  
  7. </p>    

We have completed all the coding part. We can run the application now.

Azure Redis Cache With Azure SQL In Blazor Project 

Click the “Get Pincode Details” link. It will display all the state names. You can select any state and click “Get PO Details”. I have chosen “KERALA” as the state.

Azure Redis Cache With Azure SQL In Blazor Project 
 
After some time, we will get the PO details of Kerala state. Please note, this is from the database. It took 8.5 seconds.
 
Azure Redis Cache With Azure SQL In Blazor Project 
 
We can click the “Get PO Details” button again. Please note, this is from the cache. It took only 4.7 seconds. We can see the time difference.
 
Azure Redis Cache With Azure SQL In Blazor Project 
 
We can change the state to “DELHI”. Again, data will be fetched from SQL database because we choose Delhi the first time.
 
 
We can click “Get PO Details” again within 5 minutes, we will get data from the cache.
 
Azure Redis Cache With Azure SQL In Blazor Project 
 
Conclusion
 
Please note, we are using the lowest Redis Cache plan only. If we upgrade to a higher plan, we will get more results.
 
In this post, we have seen how to create Azure Redis Cache and what caching is. We have created a Blazor app to fetch Indian post office data using Redis Cache. We have stored the post office data in Azure SQL, and we have kept the cache in 5 minutes. We have seen the time difference between cached and non-cached data. For testing purposes, I have limited the maximum rows in the grid to 100.
 
I have deployed the Blazor app to Azure. If you are interested to check the application, please run this URL. I have also added the table creation script to attached Zip file along with source code. I am expecting your valuable feedback and critiques so that I can improve more in the next articles.
 
We will see more exciting features on Blazor in upcoming posts.