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