.NET 2.0 SqlDependency 快速上手：即時資料變更通知與快取同步

SqlDependency 核心概念

SqlDependency 讓應用程式向 SQL Server 2005+ 註冊查詢通知，當查詢結果集發生變更（INSERT/UPDATE/DELETE）時，資料庫主動推送通知給應用程式，應用程式即可即時使快取失效、重新載入資料、觸發業務流程。這是查詢通知機制的高階封裝，比輪詢高效得多。

啟用必要條件

1. 資料庫啟用 Service Broker：ALTER DATABASE [YourDB] SET ENABLE_BROKER;（需獨佔存取，建議維護視窗執行）。
2. 使用者擁有 SUBSCRIBE QUERY NOTIFICATIONS 權限。
3. 查詢必須符合通知支援限制：明確列出欄位（不可 SELECT *）、使用雙部分名稱 dbo.Table、不含不支援的函數/運算子、不使用 TOP、DISTINCT、UNION 等。

兩種使用模式

模式 A：SqlCacheDependency + Cache.Insert（頁面/資料快取）

string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;
string cmdText = "SELECT ProductID, ProductName, UnitPrice FROM dbo.Products WHERE Discontinued = 0";

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
    SqlCacheDependency dep = new SqlCacheDependency(cmd);
    CacheItemRemovedCallback onRemove = (key, value, reason) => {
        if (reason == CacheItemRemovedReason.DependencyChanged)
        {
            // 可選：記錄、觸發重新載入、發送通知
            ReloadProductsCache();
        }
    };
    Cache.Insert("ProductsCache", GetProductsData(), dep,
        Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
        CacheItemPriority.Default, onRemove);
}

關鍵順序：建立 Dependency → 才執行 Command 取得資料 → 才 Insert Cache。順序錯誤會導致通知遺漏。

模式 B：SqlDependency + OnChange 事件（自訂邏輯、SignalR 推播）

SqlDependency.Start(connStr); // 應用程式啟動時呼叫一次（Global.asax Application_Start）

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
    SqlDependency dep = new SqlDependency(cmd);
    dep.OnChange += (sender, e) => {
        if (e.Type == SqlNotificationType.Change)
        {
            // 執行緒集區執行緒，非 UI 執行緒
            // 1. 使快取失效：Cache.Remove("ProductsCache");
            // 2. 重新載入：ReloadProductsCache();
            // 3. SignalR 推播給瀏覽器：Clients.All.updateProducts(GetProductsData());
        }
    };
    cmd.ExecuteReader(); // 必須執行命令才會啟動通知訂閱
}

注意：SqlDependency.Start 對同一連線字串只需呼叫一次；Stop 在 Application_End 呼叫。

完整範例：ASP.NET 應用程式整合

Global.asax

protected void Application_Start()
{
    string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;
    SqlDependency.Start(connStr);
}

protected void Application_End()
{
    string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;
    SqlDependency.Stop(connStr);
}

資料存取元件

public class ProductRepository
{
    private string _connStr;
    public ProductRepository(string connStr) { _connStr = connStr; }

    public List GetActiveProducts()
    {
        // 先嘗試從快取取得
        var cached = HttpRuntime.Cache["ActiveProducts"] as List;
        if (cached != null) return cached;

        // 快取未命中，查詢資料庫並建立相依性
        var list = new List();
        string sql = "SELECT ProductID, ProductName, UnitPrice FROM dbo.Products WHERE Discontinued = 0";
        using (SqlConnection conn = new SqlConnection(_connStr))
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            SqlCacheDependency dep = new SqlCacheDependency(cmd);
            conn.Open();
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                    list.Add(new Product { Id = dr.GetInt32(0), Name = dr.GetString(1), Price = dr.GetDecimal(2) });
            }
            HttpRuntime.Cache.Insert("ActiveProducts", list, dep,
                Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration);
        }
        return list;
    }
}

常見問題與除錯技巧

監控與診斷

• SQL Server Profiler 追蹤 Query Notification 事件（QN:Subscription、QN:Parameter、QN:Template）。
• 查詢 sys.dm_qn_subscriptions 檢視作用中訂閱。
• 記錄 OnChange 事件的 e.Type、e.Info、e.Source 進行分析。

進階：搭配 SignalR 實現即時 UI 更新

// 在 OnChange 事件中
var hubContext = GlobalHost.ConnectionManager.GetHubContext();
hubContext.Clients.All.productsUpdated(GetActiveProducts());

前端 JavaScript 接收後直接更新畫面，無需輪詢、無需重整，極佳使用者體驗。

總結

SqlDependency 是 .NET 2.0+ 實現資料庫驅動即時通知的利器。掌握「啟用 Broker → 符合查詢限制 → 正確建立相依性順序 → 處理 OnChange/移除回呼」四步驟，並善用 Profiler 診斷，即可打造高效能、即時同步的快取架構與即時應用。