ADO.NET 2.0 查詢通知：SqlDependency 實現資料快取自動失效與即時更新

痛點：快取資料如何知道資料庫已變更？

傳統三層架構常將唯讀/可修改唯讀資料表（如產品分類、稱謂代碼）快取於中間層。問題在於：資料庫異動時，中間層快取如何及時得知？

• 輪詢：資源浪費、延遲高。
• 觸發器+訊息佇列：開發維護成本高。

SQL Server 2005 + ADO.NET 2.0 內建 查詢通知 機制，徹底解決此問題。

架構總覽

1. 用戶端 SqlCommand 附加 SqlDependency，送出 TDS 封包含通知請求。
2. SQL Server 查詢引擎執行查詢，並向 Service Broker 訂閱結果集變更。
3. 資料異動 (INSERT/UPDATE/DELETE) 觸發變更偵測（沿用索引檢視同步機制）。
4. Service Broker 將通知訊息送至 msdb 內建服務 QueryNotificationService。
5. 系統預存程序 sp_DispatcherProc (CLR) 讀取佇列，透過 HTTP/TCP 推送通知給用戶端。
6. 用戶端 SqlDependency.OnChanged 事件觸發，應用程式重新查詢並刷新快取。

核心限制與特性

• 每個訂閱僅觸發一次，收到通知後必須重新訂閱。
• 通知不包含「哪些資料列變更」，僅告知「結果集已變更」。
• 查詢必須符合索引檢視限制：兩部分命名、明確列出欄位、無 OUTER JOIN、無彙總函數 等。
• 不支援 SELECT *、临时表、不確定性函數 (如 GETDATE())。
• 資料庫需啟用 Service Broker：ALTER DATABASE SET ENABLE_BROKER。
• 用戶需具備 SUBSCRIBE QUERY NOTIFICATIONS 權限。

三種使用層級

1. SqlDependency（最簡單，自動分發）

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("SELECT Au_ID, Au_LName FROM dbo.Authors", conn))
{
    SqlDependency dep = new SqlDependency(cmd);
    dep.OnChanged += OnDataChanged;

    conn.Open();
    using (SqlDataReader dr = cmd.ExecuteReader()) { /* 處理資料 */ }
    // 連線可關閉，通知仍會送達
    Console.ReadLine(); // 等待通知
}

void OnDataChanged(object sender, SqlNotificationEventArgs e)
{
    Console.WriteLine($"資料變更：Type={e.Type}, Info={e.Info}, Source={e.Source}");
    // 重新查詢並刷新快取
    RefreshCache();
}

2. SqlNotificationRequest（手動處理 Service Broker 佇列）

SqlNotificationRequest req = new SqlNotificationRequest();
req.Id = Guid.NewGuid();
req.Service = "MyCustomService"; // 需預先建立 QUEUE/SERVICE
req.Timeout = 0;

cmd.Notification = req;
// 執行查詢...

// 另一執行緒/程式輪詢佇列
using (SqlCommand waitCmd = new SqlCommand("WAITFOR(RECEIVE CONVERT(XML, message_body) FROM MyQueue)", conn))
{
    object msg = waitCmd.ExecuteScalar(); // 封鎖直到收到通知
    // 解析 XML 訊息自行處理
}

3. ASP.NET SqlCacheDependency（整合 ASP.NET 快取）

// 程式碼方式
SqlCacheDependency dep = new SqlCacheDependency(cmd);
Response.Cache.SetExpires(DateTime.Now.AddSeconds(60));
Response.AddCacheDependency(dep);

// 聲明式：頁面指示詞
// <%@ OutputCache SqlDependency="CommandNotification" Duration="60" VaryByParam="none" %>

支援 SQL Server 2000/2005（舊版透過輪詢觸發器表實作）。

通知資訊解讀

SqlNotificationEventArgs 三屬性：

XML 訊息範例：

<qn:QueryNotification id="2" type="change" source="data" info="update" 
    database_id="6" user_id="1" xmlns:qn="...">
    <qn:Message>{GUID}</qn:Message>
</qn:QueryNotification>

適用場景與反模式

✅ 適合

• 可修改唯讀表（參考資料、代碼表）。
• 中低頻率更新、多讀少寫。

❌ 不適合

• 純唯讀表（浪費資源）。
• 高頻交易表（股票價格、即時競價）：通知風暴癱瘓網路與資料庫。
• 臨時查詢（產生大量不同訂閱）。

最佳實務

• 永遠使用參數化查詢，SQL Server 會合併相同查詢計畫的訂閱，節省資源。
• 監控 sys.dm_qn_subscriptions 動態管理檢視，DBA 可清理殘留訂閱：KILL QUERY NOTIFICATION SUBSCRIPTION <id>。
• 設定合理 Timeout 避免永久佔用資源。