Enterprise Library 深入解析與靈活應用（2）：透過 SqlDependency 實現快取與資料庫同步

前言

企業級應用離不開快取，Caching Application Block 提供易用擴展框架。快取核心在於將穩定資料駐留記憶體，關鍵挑戰為負載平衡與持久層資料同步。本文提供透過 SqlDependency 實現 SQL Server 資料變更觸發快取過期的方案。

快取項目過期策略

CAB 內建多種 ICacheItemExpiration 實作：AbsoluteTime、ExtendedFormatTime、FileDependency、NeverExpired、SlidingTime。若能建立基於資料庫變更的過期策略，即可實現同步。SqlDependency 基於 Service Broker，訂閱查詢通知，資料異動觸發 Changed 事件。

實作 SqlDependencyExpiration

public class SqlDependencyExpiration : ICacheItemExpiration
{
    public event EventHandler Expired;
    public bool HasChanged { get; set; }
    public string ConnectionName { get; set; }
    private static readonly CommandType DefaultCommandType = CommandType.StoredProcedure;

    public SqlDependencyExpiration(string commandText, IDictionary<string, object> parameters)
        : this(commandText, DefaultCommandType, string.Empty, parameters) { }

    public SqlDependencyExpiration(string commandText, CommandType cmdType, string connName, IDictionary<string, object> parameters)
    {
        this.ConnectionName = string.IsNullOrEmpty(connName)
            ? DatabaseSettings.GetDatabaseSettings(ConfigurationSourceFactory.Create()).DefaultDatabase
            : connName;

        SqlDependency.Start(ConfigurationManager.ConnectionStrings[this.ConnectionName].ConnectionString);

        using (SqlConnection conn = DatabaseFactory.CreateDatabase(this.ConnectionName).CreateConnection() as SqlConnection)
        {
            SqlCommand cmd = new SqlCommand(commandText, conn) { CommandType = cmdType };
            if (parameters != null) AddParameters(cmd, parameters);

            SqlDependency dep = new SqlDependency(cmd);
            dep.OnChange += (s, e) =>
            {
                this.HasChanged = true;
                this.Expired?.Invoke(this, EventArgs.Empty);
            };

            if (conn.State != ConnectionState.Open) conn.Open();
            cmd.ExecuteNonQuery();
        }
    }

    private void AddParameters(SqlCommand cmd, IDictionary<string, object> parameters)
    {
        cmd.Parameters.Clear();
        foreach (var p in parameters)
        {
            string name = p.Key.StartsWith("@") ? p.Key : "@" + p.Key;
            cmd.Parameters.Add(new SqlParameter(name, p.Value));
        }
    }

    public bool HasExpired()
    {
        bool flag = this.HasChanged;
        this.HasChanged = false;
        return flag;
    }

    public void Initialize(CacheItem owningCacheItem) { }
    public void Notify() { }
}

關鍵點：建構函式中啟動 SqlDependency.Start、建立命令與相依性、註冊 OnChange 設定 HasChanged 並觸發 Expired 事件；HasExpired 讀取並重置旗標。

應用範例

場景：不同 User 擁有不同通知訊息，更新頻率低，適合快取。資料表 Messages，預存程序 Message_Select_By_User。

private const string UserName = "Foo";
private const string MessageCachingProcedure = "Message_Select_By_User";
private const string CacheKey = "__MessageOfFoo";

private SqlDependencyExpiration CacheItemExpiration
{
    get
    {
        var parameters = new Dictionary<string, object> { { "UserID", UserName } };
        var exp = new SqlDependencyExpiration(MessageCachingProcedure, parameters);
        exp.Expired += (s, e) => MessageBox.Show("Cache has expired!");
        return exp;
    }
}

private List<string> GetMessageByUser(string userName)
{
    var list = new List<string>();
    var db = DatabaseFactory.CreateDatabase();
    var cmd = db.GetStoredProcCommand(MessageCachingProcedure);
    db.AddInParameter(cmd, "UserID", DbType.String, userName);
    using (var reader = db.ExecuteReader(cmd))
        while (reader.Read()) list.Add(reader.GetString(2));
    return list;
}

private List<string> GetMessages()
{
    var mgr = CacheFactory.GetCacheManager();
    if (mgr.GetData(CacheKey) == null)
        mgr.Add(CacheKey, GetMessageByUser(UserName), CacheItemPriority.Normal, null, this.CacheItemExpiration);
    return mgr.GetData(CacheKey) as List<string>;
}

private void CreateMessageEntry(string userName, string message)
{
    var db = DatabaseFactory.CreateDatabase();
    var cmd = db.GetSqlStringCommand("INSERT INTO [dbo].[Messages]([UserID],[Message]) VALUES(@userID, @message)");
    db.AddInParameter(cmd, "userID", DbType.String, userName);
    db.AddInParameter(cmd, "message", DbType.String, message);
    db.ExecuteNonQuery(cmd);
}

private void buttonAdd_Click(object sender, EventArgs e)
{
    CreateMessageEntry(comboBoxUserName.SelectedValue.ToString(), textBoxMessage.Text.Trim());
    Thread.Sleep(1000); // 等待通知傳遞
    listBoxMessage.DataSource = GetMessages();
}

為 User Foo 新增訊息時，ListBox 即時更新並彈出「Cache has expired!」；為其他 User 新增則不觸發。

註：需啟用 Service Broker：ALTER DATABASE MyDb SET ENABLE_BROKER，否則會報錯。

完整原始碼可供下載參考。