您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 查詢通知與快取依賴實作教學：SqlDependency 完整啟用與除錯指南

編輯：關於SqlServer

前置作業：啟用 Service Broker

SQL Server 2005 的查詢通知機制仰賴 Service Broker。請先確認資料庫是否已啟用：

SELECT DATABASEPROPERTYEX(N'YourDB', N'IsBrokerEnabled'); -- 1=已啟用, 0=未啟用

若未啟用，需執行（注意：需獨佔存取，建議維護時段執行）：

ALTER DATABASE YourDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

應用程式端啟動偵聽器

在 ASP.NET 專案的 Global.asax 中管理連線字串的啟動/停止：

void Application_Start() {
    SqlDependency.Start(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
}
void Application_End() {
    SqlDependency.Stop(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
}

兩種快取依賴模式

模式 A：SqlCacheDependency（輪詢/快取失效模式）

適合「資料變更頻率低、可接受短暫髒讀」的情境。流程：建立命令 → 建立依賴 → 取得資料 → 存入 Cache。

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand(sql, conn)) {
    var dep = new SqlCacheDependency(cmd);
    var callback = new CacheItemRemovedCallback(OnCacheRemoved);
    conn.Open();
    var data = cmd.ExecuteReader(); // 此時已註冊通知
    Cache.Insert(cacheKey, data, dep, 
                 Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
                 CacheItemPriority.Default, callback);
}

模式 B：SqlDependency 事件驅動模式

適合「需即時反應、主動清除快取或觸發業務流程」的情境。

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand(sql, conn)) {
    var dep = new SqlDependency(cmd);
    dep.OnChange += OnDependencyChange; // 訂閱變更事件
    conn.Open();
    cmd.ExecuteReader(); // 啟動偵聽
}

void OnDependencyChange(object sender, SqlNotificationEventArgs e) {
    if (e.Type == SqlNotificationType.Change) {
        Cache.Remove(cacheKey); // 或重新載入、發送訊號等
        // 重要：事件觸發後需重新註冊新的 SqlDependency
        ReRegisterDependency();
    }
}

常見坑與除錯技巧

支援的查詢限制：必須指定資料表名稱（不可 SELECT *），不可使用 UNION、DISTINCT、TOP、外部連接等。
權限需求：使用者需具備 SUBSCRIBE QUERY NOTIFICATIONS 權限，且資料庫啟用 TRUSTWORTHY 或簽署憑證。
機器環境差異：防火牆、防毒軟體、網路隔離可能導致通知封包無法送達。善用 SQL Server Profiler 追蹤 Broker:Conversation、QN:Subscription 事件確認訊息流向。
重複通知/漏通知：確保 Application_End 正確呼叫 SqlDependency.Stop，避免殘留佇列造成重複啟動。