Table of Contents

SQL Server cache

The Sql kind stores the values in a SQL Server table. Use it when you already run SQL Server and want a shared cache that survives restarts without adding Redis to the infrastructure. It is slower than memory or Redis. This page extends the caching guide.

What it solves

SqlCache (SqlCache) wraps the Microsoft.Extensions.Caching.SqlServer distributed cache and adds the named configuration and the serialization of the values. The kind is Sql; the NuGet package is Arc4u.Caching.SqlServer and lives in the src/Arc4u.Caching.Sql folder of the repository.

Packages involved

Package Use it for
Arc4u.Caching.SqlServer SqlCache (kind Sql) and AddSqlCacheKind().
Arc4u.Caching ICacheContext and AddCacheContext.
Arc4u.Serializer.JSon The serializer (any IObjectSerialization works).

Install

dotnet add package Arc4u.Caching.SqlServer --prerelease
dotnet add package Arc4u.Serializer.JSon --prerelease

Create the cache table before the first use. Arc4u does not create it. Use the dotnet sql-cache tool of ASP.NET Core:

dotnet tool install --global dotnet-sql-cache
dotnet sql-cache create "<connection-string>" dbo SqlCache

Configuration

appsettings.json

{
  "Caching": {
    "Default": "Durable",
    "Caches": [
      {
        "Name": "Durable",
        "Kind": "Sql",
        "IsAutoStart": true,
        "Settings": {
          "ConnectionString": "Server=localhost;Database=Cache;Integrated Security=true;TrustServerCertificate=true",
          "SchemaName": "dbo",
          "TableName": "SqlCache"
        }
      }
    ]
  }
}

The settings bind to SqlCacheOption:

Key Type Default Description
Caching:Caches:n:Settings:ConnectionString string none (required) Connection string of the database that holds the table.
Caching:Caches:n:Settings:SchemaName string dbo Schema of the table.
Caching:Caches:n:Settings:TableName string SqlCache Name of the table.
Caching:Caches:n:Settings:SerializerName string none Key of the IObjectSerialization to use. When empty or not registered, the unkeyed one is used.
Caution

The connection string can hold credentials. Read it from user secrets, an environment variable (Caching__Caches__0__Settings__ConnectionString) or a secret store, not from a committed file.

Code

// Program.cs
using Arc4u.Caching;
using Arc4u.Caching.Sql;
using Arc4u.Dependency;
using Arc4u.Serializer;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddILogger();
builder.Services.AddCacheContext(builder.Configuration);
builder.Services.AddSingleton<IObjectSerialization, JsonSerialization>();
builder.Services.AddSqlCacheKind();

var app = builder.Build();
app.Run();

Common scenarios

Keep a cache across restarts

Declare a Sql cache next to a Memory cache and choose by name: durable, shared data (a computed catalog, a token store) in Durable, per-instance data in memory. See Use several caches.

Extensibility points

AddSqlCacheKind() registers SqlCache under the key Sql with TryAddKeyedTransient. To replace it, register your own ICache under that key before calling it, or instead of it.

Troubleshooting

ArgumentNullException for ConnectionString at startup

ConnectionString is required and is checked when the cache is initialized. A Sql cache with no Settings section has none.

DataCacheException on Get or Put

The table does not exist or has another name or schema. Create it with dotnet sql-cache create, then check SchemaName and TableName.

See also

Edit this page