.NET Framework 4.7.2分布式SQL缓存使用及组件适配咨询

1. NuGet包兼容性说明 #

Microsoft.Extensions.Caching.Distributed 完全支持.NET Framework 4.7.2，它的目标框架包含.NET Framework 4.6.1及以上版本，可放心使用。但仅这个抽象包不够，还需搭配具体实现包 Microsoft.Extensions.Caching.SqlServer——这是SQL Server分布式缓存的官方实现。

2. 具体实现步骤 #

• 安装必要NuGet包：通过NuGet包管理器安装以下两个包：

  • Microsoft.Extensions.Caching.Distributed
  • Microsoft.Extensions.Caching.SqlServer

• 创建SQL缓存存储结构：在SQL Server中手动执行以下脚本，创建缓存表和配套存储过程：

CREATE TABLE [dbo].[Cache](
    [Id] [nvarchar](449) NOT NULL,
    [Value] [varbinary](max) NOT NULL,
    [ExpiresAtTime] [datetimeoffset](7) NOT NULL,
    [SlidingExpirationInSeconds] [bigint] NULL,
    [AbsoluteExpiration] [datetimeoffset](7) NULL,
 CONSTRAINT [PK_Cache] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Cache_ExpiresAtTime] ON [dbo].[Cache]
(
    [ExpiresAtTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[GetCacheItem]
    @Id nvarchar(449)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT [Value], [ExpiresAtTime], [SlidingExpirationInSeconds], [AbsoluteExpiration]
    FROM [dbo].[Cache]
    WHERE [Id] = @Id;
END
GO

CREATE PROCEDURE [dbo].[SetCacheItem]
    @Id nvarchar(449),
    @Value varbinary(max),
    @ExpiresAtTime datetimeoffset(7),
    @SlidingExpirationInSeconds bigint,
    @AbsoluteExpiration datetimeoffset(7)
AS
BEGIN
    SET NOCOUNT ON;
    MERGE [dbo].[Cache] WITH (HOLDLOCK) AS target
    USING (SELECT @Id AS [Id]) AS source
    ON target.[Id] = source.[Id]
    WHEN MATCHED THEN
        UPDATE SET
            [Value] = @Value,
            [ExpiresAtTime] = @ExpiresAtTime,
            [SlidingExpirationInSeconds] = @SlidingExpirationInSeconds,
            [AbsoluteExpiration] = @AbsoluteExpiration
    WHEN NOT MATCHED THEN
        INSERT ([Id], [Value], [ExpiresAtTime], [SlidingExpirationInSeconds], [AbsoluteExpiration])
        VALUES (@Id, @Value, @ExpiresAtTime, @SlidingExpirationInSeconds, @AbsoluteExpiration);
END
GO

CREATE PROCEDURE [dbo].[DeleteCacheItem]
    @Id nvarchar(449)
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM [dbo].[Cache]
    WHERE [Id] = @Id;
END
GO

• 配置并使用缓存：在.NET Framework项目中，通过构建依赖注入容器来配置SQL缓存服务，示例代码可放在Global.asax.cs的Application_Start方法中：

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Caching.SqlServer;
using Microsoft.Extensions.Caching.Distributed;

protected void Application_Start()
{
    var services = new ServiceCollection();
    
    // 配置SQL分布式缓存
    services.AddDistributedSqlServerCache(options =>
    {
        options.ConnectionString = "你的SQL Server连接字符串";
        options.SchemaName = "dbo";
        options.TableName = "Cache";
    });

    var serviceProvider = services.BuildServiceProvider();
    
    // 获取缓存实例
    var distributedCache = serviceProvider.GetRequiredService<IDistributedCache>();
    
    // 示例：设置带过期时间的缓存
    distributedCache.SetString("test_key", "test_value", new DistributedCacheEntryOptions
    {
        AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(30)
    });
    
    // 示例：读取缓存
    var cachedValue = distributedCache.GetString("test_key");
}

3. 场景适配说明 #

你的待缓存数据容量小于512MB，SQL缓存完全能满足需求：单条缓存记录默认最大支持8MB，总容量仅受限于SQL Server数据库的存储空间。对比Redis，SQL缓存无需额外维护独立服务，可复用现有SQL Server资源；如果系统并发量不是极高，性能表现也能达标。

内容的提问来源于stack exchange，提问作者Monisha