7 Things to Know About SQL Server Distributed Caching in ASP.NET Core

In an article I wrote some time ago, I explained the essentials of in-memory caching in ASP.NET Core. Although in-memory caching serves its purpose in many small applications, at times you need distributed cache rather than local in-memory cache. Consider a situation where a web farm is serving the requests. In this case you can't guarantee that the server storing the cache will serve all the requests. You may also want your cache to survive application restarts.

Caching systems such as Redis (and its implementation on Windows) can provide in-memory distributed cache. SQL Server based caching can provide database driven caching. This article discusses how to use the SQL Server distributed caching in ASP.NET Core application.

I assume that you have create an ASP.NET Core web application as outlined in the previous part of this article.

1. You need to reference certain NuGet packages

In order to use SQL Server distributed caching you need to rely on two NuGet packages. These packages are as follows:

Microsoft.Extensions.Caching.SqlServer

Microsoft.Extensions.Caching.SqlConfig.Tools 

Adding the first package is a straightforward thing. Just right click on the Dependencies folder and click on Manage NuGet packages option. Then search for that package and hit Install button to add it to your project.

Adding the second package is bit tricky. That's because it's a CLI tool that you will use later for creating the SQL Server table. To add that tool, open the .csproj file and carefully add its entry as shown below :

....
 <ItemGroup>
    <DotNetCliToolReference 
         Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" 
         Version="1.0.0" />
    <DotNetCliToolReference 
         Include="Microsoft.Extensions.Caching.SqlConfig.Tools" 
         Version="1.0.1" />
</ItemGroup>
....

Once you add the NuGet packages, build the project so that packages will be restored and made available for further use. 

2. You need to create a database table that stores the cache items

The SQL Server distributed cache stores the items in a table. The table needs to have a specific schema in terms of column names and data types. To create this table you will use the sql-cache command line tool. To run that tool open Visual Studio 2017 command prompt, go to the project's folder and then issue the following command :

dotnet sql-cache create 
"Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True;" 
dbo MySQLCache

In the above "create" command you specify the database connection string of the database acting as the cache item data store. The connection string is followed by table schema and a table name of your choice.

A successful run of the above command will create MySQLCache table inside MyDatabase. The following figure shows the structure of this table :

Observe the columns of  the table. A cached item is stored as a binary data with its string key. Sliding expiration and absolute expiration values (if any) are also stored for that item.

3. SQL Server distributed caching needs to enabled in the Startup class

Now that you have created the required table, let's add it to the services collection in the Startup class.

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();
    services.AddDistributedSqlServerCache(options =>
    {
        options.ConnectionString = @"Data Source=.;
Initial Catalog=Northwind;Integrated Security=True;";
        options.SchemaName = "dbo";
        options.TableName = "MySQLCache";
    });
}

The AddDistributedSqlServerCache() method registers the cache object with the DI framework. While adding the SQL Server distributed cache a few options are configured. The database connection string, table schema and table name are specified as the configuration information. 

4. SQL Server distributed caching uses dependency injection to inject the IDistributedCache object

The easiest way to use the SQL Server distributed cache is to inject it to the controller. The following code shows how this is done :

public class HomeController : Controller
{
    private IDistributedCache cache;

    public HomeController(IDistributedCache cache)
    {
        this.cache = cache;
    }
    ....
}

The code declares a variable of IDistributedCache. The SQL Server distributed cache implements this interface. We use interface rather than concrete object so that future changes to the caching system will be easy. The constructor of the HomeController receives IDistributedCache object through DI.

The IDistributedCache interface consists of methods such as Set(), SetString(), Get(), GetString(), Remove() and Refresh(). Once you receive the IDistributedCache object you can call these methods on it from the other actions.

5. You can use SetString() and Set() methods to store an item in the cache

Now let's see how you can store an item in the cache. There are two methods - Set() and SetString() - that allow you to add an entry to the cache. The Set() method takes a string key and a value as byte[]. The SetString() takes a string key and a string value.

Consider the following code that illustrates how Set() can be used :

string value = DateTime.Now.ToString();
byte[] data = Encoding.UTF8.GetBytes(value);
cache.Set("timestamp", data);

The code stores current date and time in a string variable. The string is converted into a byte[] using GetBytes() method. Finally, an entry is added to the cache using the Set() method.

Consider the following code that illustrates how SetString() can be used :

string value = DateTime.Now.ToString();
cache.SetString("timestamp", value);

As you can see, using SetString() is quite straightforward.

You might be wondering how objects can be stored in the cache. There are two approaches to do this - serialize objects into a byte[] and then store into the cache. Or convert objects into their string representation and then store in the cache. Let's use the later approach to store an item in the cache.

Employee emp = new Employee();
emp.EmployeeID = 1;
emp.FirstName = "Nancy";
emp.LastName = "Davolio";
string value = JsonConvert.SerializeObject(emp);
cache.SetString("employee", value);

The code creates an Employee object and sets its properties. The code then uses JsonConvert class (Json.Net component) to convert the Employee object into a JSON string. The JSON string is then stored into the cache using SetString() method. Make sure to add Json.Net NuGet package before writing this code. 

The following figure shows an item stored in the MySQLCache table :

6. You can use GetString() and Get() methods to retrieve an item from the cache

The counterpart of Set() and SetString() methods is Get() and GetString() methods. The Get() methods accepts a string key and returns its value as a byte[]. If the key doesn't exist null is returned. On the sane lines GetString() method accepts a string key and returns a value in string format. The following code shows how these methods can be used :

byte[] data = cache.Get("timestamp");
string value = Encoding.UTF8.GetString(data);

string timestamp = cache.GetString("timestamp");

Notice the use of GetString() for converting the byte[] into a string.

If you wish to add an item only if it doesn't exist then you would do that checking like this :

if (cache.GetString("timestamp") == null)
{
    cache.SetString("timestamp", 
      DateTime.Now.ToString(), options);
}

7. You can set absolute and sliding expiration on a cached item

As with the in-memory cache you can also set absolute expiration and sliding expiration for SQL Server distributed cache. The following code shows how :

DistributedCacheEntryOptions options = 
new DistributedCacheEntryOptions();
options.AbsoluteExpiration = DateTime.Now.AddMinutes(2);
options.SlidingExpiration = TimeSpan.FromMinutes(2);
            
cache.SetString("timestamp", 
DateTime.Now.ToString(), options);

The above code creates a DistributedCacheEntryOptions object and sets its AbsoluteExpiration and SlidingExpiration properties. The DistributedCacheEntryOptions object is then passed as the third parameter of the SetString() method.

That's it for now! You can also try Remove() and Refresh() methods of IDistributedCache to see how a cached items can be removed and refreshed respectively. For more details about distributed caching go here.