Implementing Distributed Cache on SQL Server using .Net
Tags: csharp, dotnet, dotnetcore
Published: 2020-06-02 11:16:40

---
title: Implementing Distributed Cache on SQL Server using .Net
published: true
description: The different types of cache and how to implement them.
tags: Csharp, dotnet, dotnetcore
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/rkaagyoptb32wn7wag0t.png
---
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](https://docs.microsoft.com/en-us/aspnet/core/performance/caching/distributed?view=aspnetcore-3.1#distributed-memory-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;
```csharp
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
```csharp
public interface ICache<T> where T: class
    {
        Task<T> GetCached(string Key);
        Task SetCache(string Key,T entity);
        Task RefreshCache(string Key);

    }
```
- Handler
```csharp
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;
```csharp
private readonly ICache<IEnumerable<Members>> _distcache;

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

```
Then the implimentation
```csharp
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](https://docs.microsoft.com/en-us/aspnet/core/performance/caching/distributed?view=aspnetcore-3.1#distributed-redis-cache) Which is pretty fast.