783
技術社區[雲棲]
Enterprise Library深入解析與靈活應用(2): 通過SqlDependency實現Cache和Database的同步
- AbsoluteTime：為cache item設置一個cache item的絕對過期時間。
- ExtendedFormatTime：通過一個表達式實現這樣的過期策略：每分鍾過期（*****：5個*分別代表minute、hour、date、month、year）；每個小時的第5分鍾過期（5****）；每個月的2號零點零分過期（0 0 2 * *）。
- FileDependency：將cache item和一個file進行綁定，通過檢測file的最後更新時間確定file自cache item被添加以來是否進行過更新。如果file已經更新則cache item過期。
- NeverExpired：永不過期。
- SlidingTime：一個滑動的時間，cache item的每次獲取都將生命周期延長到設定的時間端，當cache item最後一次獲取的時間算起，超出設定的時間，則cache item過期。
1: public interface ICacheItemExpiration
2: {
3: // Methods
4: bool HasExpired();
5: void Initialize(CacheItem owningCacheItem);
6: void Notify();
7: }
1: namespace Artech.SqlDependencyCaching
2: {
3: public class SqlDependencyExpiration : ICacheItemExpiration
4: {
5: private static readonly CommandType DefaultComamndType = CommandType.StoredProcedure;
6:
7: public event EventHandler Expired;
8:
9: public bool HasChanged
10: { get; set; }
11:
12: public string ConnectionName
13: { get; set; }
14:
15: public SqlDependencyExpiration(string commandText, IDictionary<string, object> parameters) :
16: this(commandText, DefaultComamndType, string.Empty, parameters)
17: { }
18:
19: public SqlDependencyExpiration(string commandText, string connectionStringName, IDictionary<string, object> parameters) :
20: this(commandText, DefaultComamndType, connectionStringName, parameters)
21: { }
22:
23: public SqlDependencyExpiration(string commandText, CommandType commandType, IDictionary<string, object> parameters) :
24: this(commandText, commandType, string.Empty, parameters)
25: { }
26:
27: public SqlDependencyExpiration(string commandText, CommandType commandType, string connectionStringName, IDictionary<string, object> parameters)
28: {
29: if (string.IsNullOrEmpty(connectionStringName))
30: {
31: this.ConnectionName = DatabaseSettings.GetDatabaseSettings(ConfigurationSourceFactory.Create()).DefaultDatabase;
32: }
33: else
34: {
35: this.ConnectionName = connectionStringName;
36: }
37:
38: SqlDependency.Start(ConfigurationManager.ConnectionStrings[this.ConnectionName].ConnectionString);
39: using (SqlConnection sqlConnection = DatabaseFactory.CreateDatabase(this.ConnectionName).CreateConnection() as SqlConnection)
40: {
41: SqlCommand command = new SqlCommand(commandText, sqlConnection);
42: command.CommandType = commandType;
43: if (parameters != null)
44: {
45: this.AddParameters(command, parameters);
46: }
47: SqlDependency dependency = new SqlDependency(command);
48: dependency.OnChange += delegate
49: {
50: this.HasChanged = true;
51: if (this.Expired != null)
52: {
53: this.Expired(this, new EventArgs());
54: }
55: };
56: if (sqlConnection.State != ConnectionState.Open)
57: {
58: sqlConnection.Open();
59: }
60: command.ExecuteNonQuery();
61: }
62: }
63:
64: private void AddParameters(SqlCommand command, IDictionary<string, object> parameters)
65: {
66: command.Parameters.Clear();
67: foreach (var parameter in parameters)
68: {
69: string parameterName = parameter.Key;
70: if (!parameter.Key.StartsWith("@"))
71: {
72: parameterName = "@" + parameterName;
73: }
74:
75: command.Parameters.Add(new SqlParameter(parameterName, parameter.Value));
76: }
77: }
78:
79: #region ICacheItemExpiration Members
80:
81: public bool HasExpired()
82: {
83: bool indicator = this.HasChanged;
84: this.HasChanged = false;
85: return indicator;
86: }
87:
88: public void Initialize(CacheItem owningCacheItem)
89: { }
90:
91: public void Notify()
92: { }
93:
94: #endregion
95: }
96: }
97:
1: private static readonly CommandType DefaultComamndType = CommandType.StoredProcedure;
2:
3: public event EventHandler Expired;
4:
5: public bool HasChanged
6: { get; set; }
7:
8: public string ConnectionName
9: { get; set; }
10:
1: if (string.IsNullOrEmpty(connectionStringName))
2: {
3: this.ConnectionName = DatabaseSettings.GetDatabaseSettings(ConfigurationSourceFactory.Create()).DefaultDatabase;
4: }
5: else
6: {
7: this.ConnectionName = connectionStringName;
8: }
1: SqlDependency dependency = new SqlDependency(command);
2: dependency.OnChange += delegate
3: {
4: this.HasChanged = true;
5: if (this.Expired != null)
6: {
7: this.Expired(this, new EventArgs());
8:
9: }
10:
11: };
12:
1: public bool HasExpired()
2: {
3: bool indicator = this.HasChanged;
4: this.HasChanged = false;
5: return indicator;
6: }
1: ALTER PROCEDURE [dbo].[Message_Select_By_User]
2: (@UserID VarChar(50))
3: AS
4: BEGIN
5: Select ID, UserID, [Message] From dbo.Messages Where UserID = @UserID
6: END
注：如何寫成Select * From dbo.Messages Where UserID = @UserID, SqlDependency 將不能正常運行；同時Table的也是必須的。
1: private const string UserName = "Foo";
2: private const string MessageCachingProcedure = "Message_Select_By_User";
3: private const string CacheKey = "__MessageOfFoo";
1: private SqlDependencyExpiration CacheItemExpiration
2: {
3: get
4: {
5: IDictionary<string, object> parameters = new Dictionary<string, object>();
6: parameters.Add("UserID", UserName);
7: SqlDependencyExpiration expiration= new SqlDependencyExpiration(MessageCachingProcedure, parameters);
8: expiration.Expired += delegate
9: {
10: MessageBox.Show("Cache has expired!");
11: };
12:
13: return expiration;
14: }
15: }
16:
1: private List<string> GetMessageByUser(string userName)
2: {
3: List<string> messageList = new List<string>();
4: Database db = DatabaseFactory.CreateDatabase();
5: DbCommand command = db.GetStoredProcCommand(MessageCachingProcedure);
6: db.AddInParameter(command, "UserID", DbType.String, userName);
7: IDataReader reader = db.ExecuteReader(command);
8: while (reader.Read())
9: {
10: messageList.Add(reader.GetString(2));
11: }
12:
13: return messageList;
14: }
1: private List<string> GetMessages()
2: {
3: ICacheManager manager = CacheFactory.GetCacheManager();
4: if (manager.GetData(CacheKey) == null)
5: {
6: manager.Add(CacheKey, GetMessageByUser(UserName), CacheItemPriority.Normal, null, this.CacheItemExpiration);
7: }
8:
9: return manager.GetData(CacheKey) as List<string>;
10: }
1: private void CreateMessageEntry(string userName, string message)
2: {
3: Database db = DatabaseFactory.CreateDatabase();
4: string insertSql = "INSERT INTO [dbo].[Messages]([UserID],[Message])VALUES(@userID, @message)";
5: DbCommand command = db.GetSqlStringCommand(insertSql);
6: db.AddInParameter(command, "userID", DbType.String, userName);
7: db.AddInParameter(command, "message", DbType.String, message);
8: db.ExecuteNonQuery(command);
9: }
1: private void buttonAdd_Click(object sender, EventArgs e)
2: {
3: this.CreateMessageEntry(this.comboBoxUserName.SelectedValue.ToString(), this.textBoxMessage.Text.Trim());
4: Thread.Sleep(1000);
5: this.listBoxMessage.DataSource = this.GetMessages();
6: }
1: ALTER DATABASE MyDb SET ENABLE_BROKER ;
Enterprise Library深入解析與靈活應用:
[1] 通過Unity Extension實現和Policy Injection Application Block的集成
[2] 通過SqlDependency實現Cache和Database的同步
[3] 倘若將Unity、PIAB、Exception Handling引入MVP模式
[4] 創建一個自定義Exception Handler改變ELAB的異常處理機製
[5] 創建一個簡易版的批處理執行器，認識Enterprise Library典型的配置方式和對象創建方式
[6] 自己動手創建迷你版AOP框架
[7] 再談PIAB與Unity之間的集成
作者：蔣金楠
微信公眾賬號：大內老A
微博：www.weibo.com/artech
如果你想及時得到個人撰寫文章以及著作的消息推送，或者想看看個人推薦的技術資料，可以掃描左邊二維碼（或者長按識別二維碼）關注個人公眾號（原來公眾帳號蔣金楠的自媒體將會停用）。
本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁麵明顯位置給出原文連接，否則保留追究法律責任的權利。
微信公眾賬號：大內老A
微博：www.weibo.com/artech
如果你想及時得到個人撰寫文章以及著作的消息推送，或者想看看個人推薦的技術資料，可以掃描左邊二維碼（或者長按識別二維碼）關注個人公眾號（原來公眾帳號蔣金楠的自媒體將會停用）。
本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁麵明顯位置給出原文連接，否則保留追究法律責任的權利。
最後更新：2017-10-30 17:04:26