ASP.NET SQL 快取相依性實戰：資料庫變更觸發快取失效完整方案

核心問題：為何需要 SQL 快取相依性？

傳統 Cache.Insert(key, value, CacheDependency) 僅支援**檔案/目錄/鍵值**相依。當資料來源為 SQL Server 資料表，且需**「資料異動即時讓快取失效」**時，ASP.NET 2.0+ 內建 **SqlCacheDependency** 機制，透過 **Service Broker / 查詢通知 / 輪詢** 三種模式實現。

一、三種技術模式對比

二、方案一：SqlDependency (推薦現代專案)

2.1 資料庫端設定

-- 1. 啟用 Service Broker
ALTER DATABASE Northwind SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

-- 2. 授權
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [AppUser];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [AppUser];

-- 3. 確認資料表符合限制：需有主鍵、不含 *、不含非確定性函數
SELECT EmployeeID, LastName, FirstName FROM dbo.Employees; -- 合法查詢

2.2 應用程式端完整實作

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Caching;

public class EmployeeRepository
{
    private static readonly string ConnStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
    private const string CacheKey = "Employees_All";
    private const string Sql = "SELECT EmployeeID, LastName, FirstName FROM dbo.Employees";

    public static DataTable GetEmployees()
    {
        // 1. 嘗試從快取取得
        if (HttpRuntime.Cache[CacheKey] is DataTable cached)
            return cached;

        // 2. 快取未命中，查詢資料庫並建立相依性
        using (var conn = new SqlConnection(ConnStr))
        using (var cmd = new SqlCommand(Sql, conn))
        {
            // 關鍵：SqlCommand 必須關聯 SqlDependency
            var dep = new SqlCacheDependency(cmd);
            
            conn.Open();
            var dt = new DataTable();
            using (var adapter = new SqlDataAdapter(cmd))
                adapter.Fill(dt);

            // 3. 插入快取，附加 SqlCacheDependency
            HttpRuntime.Cache.Insert(CacheKey, dt, dep, 
                Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, 
                CacheItemPriority.Normal, OnCacheRemoved);

            return dt;
        }
    }

    // 4. 快取移除回呼：記錄日誌、預熱、通知其他節點
    private static void OnCacheRemoved(string key, object value, CacheItemRemovedReason reason)
    {
        _logger.Info($"Cache {key} removed: {reason}");
        if (reason == CacheItemRemovedReason.DependencyChanged)
        {
            // 非同步預熱下一筆，避免下一請求穿透 DB
            Task.Run(() => GetEmployees());
        }
    }
}

2.3 Global.asax 啟動監聽器 (必要！)

protected void Application_Start()
{
    // 啟動 SqlDependency 監聽器 (每個連線字串僅需啟動一次)
    SqlDependency.Start(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);
}

protected void Application_End()
{
    SqlDependency.Stop(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);
}

三、方案二：輪詢 + 檔案觸發器 (相容 SQL 2000 / 無 Broker 環境)

此為文章原始方案現代化改良版，核心思想：**資料庫觸發器更新「信號檔案」 → ASP.NET 以 CacheDependency 監視該檔案**。

3.1 資料庫端：建立信號檔案與觸發器

-- 1. 建立共享資料夾 \\CacheServer\SqlSignals\ 並授予 SQL Server 服務帳號寫入權限
-- 2. 建立空白信號檔案
EXEC xp_cmdshell 'type nul > \\CacheServer\SqlSignals\Employees.sig';

-- 3. 建立觸發器
CREATE TRIGGER trg_Employees_NotifyCache
ON dbo.Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- 更新檔案時間戳記即可觸發 CacheDependency
    EXEC xp_cmdshell 'copy /b \\CacheServer\SqlSignals\Employees.sig+,, > nul';
END;

3.2 Web.config 設定輪詢間隔

<system.web>
  <caching>
    <sqlCacheDependency pollTime="5000" /> <!-- 5 秒輪詢一次 -->
  </caching>
</system.web>

3.3 程式碼使用 (與 SqlDependency 近似)

public static DataTable GetEmployeesPolling()
{
    const string key = "Employees_Polling";
    if (HttpRuntime.Cache[key] is DataTable cached) return cached;

    using (var conn = new SqlConnection(ConnStr))
    using (var cmd = new SqlCommand("SELECT EmployeeID, LastName, FirstName FROM dbo.Employees", conn))
    {
        // 建立檔案相依性
        var dep = new CacheDependency(@"\\CacheServer\SqlSignals\Employees.sig");
        
        conn.Open();
        var dt = new DataTable();
        new SqlDataAdapter(cmd).Fill(dt);
        
        HttpRuntime.Cache.Insert(key, dt, dep, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration);
        return dt;
    }
}

四、Web Farm / 多節點部署關鍵點

1. 信號檔案置於共享儲存 (SMB/NFS/Azure Files)，所有 Web 節點掛載相同 UNC 路徑。
2. SqlDependency：每個節點各自啟動 SqlDependency.Start，Service Broker 會將通知**廣播**至所有訂閱者，天然支援多節點。
3. 快取一致性：單節點快取失效不影響其他節點；若需強一致，引入 **Redis 分散式快取** + **Pub/Sub 失效廣播**。

五、效能調優與監控

六、常見錯誤排查

七、現代替代方案：Redis + 變更資料捕獲 (CDC)

對於高併發、微服務架構，建議棄用進程內 HttpRuntime.Cache，改用：

• Redis 做分散式快取。
• SQL Server CDC / Debezium / EF Core Interceptors 捕獲資料變更。
• Redis Pub/Sub 或 **Message Queue** 廣播失效事件至所有服務實例。

// 伪代码：CDC 事件處理器
public class SqlChangeHandler : INotificationHandler
{
    private readonly IDatabase _redis;
    public async Task Handle(SqlChangedEvent evt, CancellationToken ct)
    {
        var keys = _redis.SearchKeys($"cache:{evt.Table}:*");
        await _redis.KeyDeleteAsync(keys);
        // 可選：發布失效事件供其他微服務
        await _redis.PublishAsync("cache:invalidate", JsonSerializer.Serialize(new { evt.Table, Keys = keys }));
    }
}