用 SqlCacheDependency 快取 LINQ to SQL 的查詢結果

在 ADO.NET 2.0 有個 Query notifications (SqlDependency) 機制，讓 SQL Server 2005 能夠主動通知你的應用程式(Application)來源資料是否變更，尤其是在做資料快取(Cache)的時候特別有效率。但自從改用了 LINQ to SQL 好像就很少人提到如何利用 SqlCacheDependency 類別進行 LINQ to SQL 查詢後的結果做快取。

最近在 MSDN Code Gallery 發現有個 Caching LinqToSql results with SqlCacheDependency 專案，上面提供了一段程式碼，是以 Extension Method 的方式寫成的，可以針對 IQueryable 物件進行資料快取的動作。由於他是用一段原始碼來說明如何快取 LINQ to SQL 的結果，所以我也打算用一段程式碼說明一遍（當然是用中文說明），大部分的程式碼都跟原網站的一樣，只是我修的更完整一點，整段複製回去就可以馬上用了，你可以先在 App_Code 目錄下先建立一個空的 Class 檔（LinqToSqlCache.cs），然後將以下這段程式碼複製、貼上就可以用了。

using System;
using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Caching;

public static class LinqToSqlCache
{
    /// <summary>
    /// 快取 LINQ Query 的結果（僅適用於 LINQ to SQL 環境）
    /// 使用的的限制跟使用 SqlCacheDependency 的限制一樣
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="q"></param>
    /// <param name="dc">你的 LINQ to SQL DataContext</param>
    /// <param name="CacheId">Cache ID，需為每一個不同的 IQueryable 物件設定一個唯一的 ID</param>
    /// <returns></returns>
    public static List<T> LinqCache<T>(this IQueryable<T> q, DataContext dc, string CacheId)
    {
        List<T> objCache = (List<T>)System.Web.HttpRuntime.Cache.Get(CacheId);

        if (objCache == null)
        {
            ///////// 尚未快取，實做 new SqlCacheDependeny //////////

            // 1. 透過 DataContext 取得連線字串
            string connStr = dc.Connection.ConnectionString;

            // 2. 透過 DataContext 與 IQueryable 物件取得 SqlCommand 物件
            SqlCommand sqlCmd = dc.GetCommand(q) as SqlCommand;

            // 3. 建立要給 SqlCacheDependency 使用的 SqlConnection 物件
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                // 4. 建立要給 SqlCacheDependency 使用的 SqlCommand 物件
                using (SqlCommand cmd = new SqlCommand(sqlCmd.CommandText, conn))
                {
                    // 5.0 將 sqlCmd 中的所有參數傳遞給 cmd 物件
                    foreach (System.Data.Common.DbParameter dbp in sqlCmd.Parameters)
                    {
                        cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value));
                    }

                    // 5.1 啟用資料庫的 Query notifications 功能
                    SqlCacheDependencyAdmin.EnableNotifications(connStr);

                    // 5.2 取得要進行異動通知的表格名稱(ElementType)
                    string NotificationTable = q.ElementType.Name;

                    // 5.3 將取得的 NotificationTable 啟用通知功能
                    if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable))
                        SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable);

                    // 6. 建立 SqlCacheDependency物件
                    SqlCacheDependency sqlDep = new SqlCacheDependency(cmd);

                    // 7. 刷新 LINQ to SQL 的值（取得資料庫中的最新資料）
                    dc.Refresh(RefreshMode.OverwriteCurrentValues, q);

                    // 8. 執行 SqlCacheDepency 查詢
                    cmd.ExecuteNonQuery();

                    // 9. 執行 LINQ to SQL 的查詢，並將結果轉成 List<T> 型別，避免延遲查詢(Delayed Query)立即將資料取回
                    objCache = q.ToList();

                    //10. 將結果插入到 System.Web.HttpRuntime.Cache 物件中，並且指定 SqlCacheDependency 物件
                    System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, sqlDep);
                }
            }
        }

        // 回傳查詢結果（或快取的結果）
        return objCache;
    }
}

除了新增了 LinqToSqlCache.cs 到 App_Code 目錄下以外，還必須修改 Global.asax 檔案，在 Application_Start 與 Application_End 事件中分別加入 System.Data.SqlClient.SqlDependency.Start(connstr); 與 System.Data.SqlClient.SqlDependency.Stop(connstr); 用以啟用 ASP.NET 的 SqlDependency 支援。

void Application_Start(object sender, EventArgs e) {
    string connstr = System.Web.Configuration.WebConfigurationManager
        .ConnectionStrings["ConnectionString1"].ConnectionString;
    System.Data.SqlClient.SqlDependency.Start(connstr);
}
void Application_End(object sender, EventArgs e) {
    string connstr = System.Web.Configuration.WebConfigurationManager
        .ConnectionStrings["ConnectionString1"].ConnectionString;
    System.Data.SqlClient.SqlDependency.Stop(connstr);
}

當然除了這點以外，資料庫也需要特別設定過才能讓 SqlDependency 正常運作，如果你沒有特別設定 SQL Server 2005 的話，第一次執行就會出現「尚未啟用目前資料庫的 SQL Server Service Broker，因此不支援查詢通知。如果您想要使用通知，請啟用這個資料庫的 Service Broker。」的錯誤訊息，如下圖：

尚未啟用目前資料庫的 SQL Server Service Broker，因此不支援查詢通知。如果您想要使用通知，請啟用這個資料庫的 Service Broker。

如果要啟用 SQL Server Service Broker 的話，可以用 ALTER DATABASE 指令進行啟用 Service Broker 的動作：

ALTER DATABASE [YourDBName] SET ENABLE_BROKER

執行完後可以用以下指令查詢目前 SQL Server 2005 中有哪些資料庫已經啟用 Service Broker：

SELECT name, is_broker_enabled FROM sys.databases

但你必須注意一點，當你在執行 ALTER DATABASE [YourDBName] SET DISABLE_BROKER 指令的時候可能會發現該指令一直執行都不會停止，那是因為在任何資料庫中啟用 SQL Server Service Broker 時都需要資料庫鎖定，而你要啟用 Service Broker 的資料庫可能還在被 Management Studio 或 ASP.NET 使用中，你可以透過 Management Studio 的 管理 -> 活動監視器 功能強制將正連接到你資料庫的連線切斷即可（如下圖示）。

依照上述說明去做應該是會設定成功，接下來若要套用 LinqCache 這個 Extension Method 在你現成的專案裡就很容易了，例如你原本的程式如下：

var q = from n in db.Members
    where n.IsEnabled == true
    select n;

ListView1.DataSource = q;
ListView1.DataBind();

改成這樣就好了：

var q = from n in db.Members
    where n.IsEnabled == true
    select n;

ListView1.DataSource = q.LinqCache(db, "FrontSite_EnabledMembers");
ListView1.DataBind();

這實在是個很不錯的 LINQ to SQL Cache 解決方案呢！

2009-07-15 補充說明

由於上線中的資料庫經常會被鎖定以導致無法正確執行上述指令，建議可利用以下語法先將連線佔領，然後再執行 ENABLE_BROKER 的變更：

USE [master]
GO
ALTER DATABASE [YourDBName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [YourDBName] SET  SINGLE_USER 
GO
ALTER DATABASE [YourDBName] SET ENABLE_BROKER 
GO
ALTER DATABASE [YourDBName] SET  MULTI_USER 
GO

相關連結

The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享