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.