Implementing Distributed Cache on SQL Server using .Net

When considering Software Architecture it's always standard practice to implement some sort of Cache which will reduces the request load on the primary database. So, the question is which Cache system to implement and how? We'll show case this in a two part series.

So, let's break down the main types of cache;

• Distributed Cache

Commonly regarded as Disk Cache(though not completely true), It's commonly implemented for services which either; share cache with other services or the data cached grows rapidly.

• Memory Cache

This is the most common form of Cache implementation in most systems. The cache is stored In Memory which is not persistent. This means if a service/server restarts all the cache is lost.

So how do we implement it?

Distributed Cache

Remember, when I said it's commonly regarded as Disk Cache and it's not completely true. Well it's because we have In Memory Distributed Cache in .Net Core.

For this example I'll use DistributedSQLCache.
So, let's start with the .Net Core API;
We start by installing the following package -Microsoft.Extensions.Caching.SqlServer

Next we create SQLCache Table;
dotnet sql-cache create "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DistCache;Integrated Security=True;" dbo TblCache

We add Service Dependecy Injection;

services.AddDistributedSqlServerCache(options =>
            {
                options.ConnectionString = "Database-Connection";
                options.SchemaName = "dbo";
                options.TableName = "TblCache";
            });

Since Cache storage is in Key-Value format the Key is usually a string and Value is a byte array. So we need a Cache Handler class that converts objects to byte array as shown below.

• Interface

public interface ICache<T> where T: class
    {
        Task<T> GetCached(string Key);
        Task SetCache(string Key,T entity);
        Task RefreshCache(string Key);

    }

• Handler

public class CacheProvider<T> : ICache<T>
                           where T: class
    {

        private readonly IDistributedCache _cache;

        public CacheProvider(IDistributedCache cache)
        {
            this._cache = cache;
        }

        public async Task<T> GetCached(string Key)
        {
            if (!String.IsNullOrEmpty(Key))
            {
                byte[] cached_data = await this._cache.GetAsync(Key);

                if(cached_data != null)
                {
                    // convert to object
                    T item = GetObject(cached_data);
                    return item;
                }
                return (T)null;
            }
            else
            {
                throw new ArgumentNullException("Cache Key can't be null");
            }
        }

        public async Task RefreshCache(string Key)
        {
            if (!String.IsNullOrEmpty(Key))
            {
                await this._cache.RefreshAsync(Key);
            }
        }

        public async Task SetCache(string Key, T entity)
        {
            if (entity != null && !String.IsNullOrEmpty(Key))
            {
                //convert to byte array
                byte[] data = GetBytes(entity);
                var options = new DistributedCacheEntryOptions()
                                        .SetSlidingExpiration(TimeSpan.FromDays(60));
                await this._cache.SetAsync(Key, data, options);
            }
            else
            {
                throw new ArgumentNullException("Set Cache can't have null values");
            }
        }

        private byte[] GetBytes(T obj)
        {
            if (obj == null)
                return null;
            BinaryFormatter bf = new BinaryFormatter();
            using (MemoryStream ms = new MemoryStream())
            {
                bf.Serialize(ms, obj);
                return ms.ToArray();
            }
        }

        private T GetObject(byte[] data)
        {
            if (data == null)
                return default(T);
            BinaryFormatter bf = new BinaryFormatter();
            using (MemoryStream ms = new MemoryStream(data))
            {
                object obj = bf.Deserialize(ms);
                return (T)obj;
            }
        }
    }

Note

SetSlidingExpiration(TimeSpan.FromDays(60)) can be used to set the lifespan of the Cache.

With that let's add the Cache Handler dependency;
services.AddScoped(typeof(ICache<>), typeof(CacheProvider<>));

Finally, we can just implement it on the controller;
Dependency Injection ofcourse;

private readonly ICache<IEnumerable<Members>> _distcache;

public ActisureController(ICache<IEnumerable<Members>> distcache)
{
       this._distcache = distcache;
}

Then the implimentation

public async ValueTask<IActionResult> GetMember(string idno)
        {
            IEnumerable<Members> memberDtos= null;
            try
            {
                memberDtos = await this._distcache.GetCached(idno);
                if(memberDtos== null)
                {
                    //get members from DB
                    if(memberDtos!= null)
                    {
                        await this._distcache.SetCache(idno, memberDtos.ToList());
                    }

                }
                else
                {
                    await this._distcache.RefreshCache(idno);
                }

                return Ok(memberDtos);
            }
            catch (Exception ex)
            {

                return BadRequest(ex.Message);
            }
        }

And That's it you have a distributed Cache. You can also use this implementation for REDIS Which is pretty fast.

Comments

[Vikas-jk]

Good post, when you are using distributed cache.
if anyone want's to check in-memory cache sample in .NET Core or .NET, check
In Memory cache C# (Explanation with example in .NET and .NET Core)

Thanks