本文是在Artech“[原创]Enterprise Library深入解析与灵活应用(2): 通过SqlDependency实现Cache和Database的同步”的基础之上，将其示例移植到webform环境中而已，详细原理还请大家见Artech的文章
应用场景:利用Enlib4.1的缓存模块，实现常用数据的缓存，同时借助SqlDependency通过"监控数据是否有改动"来决定缓存是不是过期。
1.测试数据表及数据(sql2005环境)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2.实现自己的SqlDependencyCacheItemExpiration类，这里直接用Artech的代码，就不重复贴出来了
3.webform调用
1 using System;
2 using System.Collections.Generic;
3 using System.Data;
4 using System.Data.Common;
5 using Microsoft.Practices.EnterpriseLibrary.Caching;
6 using Microsoft.Practices.EnterpriseLibrary.Caching.Expirations;
7 using Microsoft.Practices.EnterpriseLibrary.Data;
8
9 namespace WebApp
10 {
11 public partial class _Default : System.Web.UI.Page
12 {
13
14 string CacheKey="JIMMY_Message_SqlDependency_Cache";
15
16
17 protected void Page_Load(object sender, EventArgs e)
18 {
19 if (!IsPostBack)
20 {
21 this.Repeater1.DataSource = GetMessages("JIMMY");
22 this.Repeater1.DataBind();
23 }
24 }
25
26 /// <summary>
27 /// 从数据库实时查询指定用户的Message
28 /// </summary>
29 /// <param name="userName"></param>
30 /// <param name="sql"></param>
31 /// <returns></returns>
32 private DataTable GetMessageByUserId(string userName,string sql)
33 {
34 Database db = DatabaseFactory.CreateDatabase();
35 if (sql.Length==0)
36 {
37 sql = "Select [ID],[UserID],[Message] From [dbo].[Messages] Where [UserID]=@UserID";
38 }
39 DbCommand command = db.GetSqlStringCommand(sql);
40 db.AddInParameter(command, "UserID", DbType.String, userName);
41 return db.ExecuteDataSet(command).Tables[0];
42
43 }
44
45 /// <summary>
46 /// 获取用户的Message(缓存优先，数据库其次)
47 /// </summary>
48 /// <param name="userName"></param>
49 /// <returns></returns>
50 private DataTable GetMessages(string userName)
51 {
52 ICacheManager manager = CacheFactory.GetCacheManager();
53 if (manager.GetData(CacheKey) == null)
54 {
55 string sql = "Select [ID],[UserID],[Message] From [dbo].[Messages] Where [UserID]=@UserID";
56
57 Dictionary<string, object> Dic = new Dictionary<string, object>();
58 Dic.Add("UserID", userName);
59 manager.Add(CacheKey, GetMessageByUserId(userName, sql), CacheItemPriority.Normal, null, new SqlDependencyExpiration(sql,CommandType.Text, Dic));
60 }
61
62 return manager.GetData(CacheKey) as DataTable;
63 }
64 }
65 }
66
2 using System.Collections.Generic;
3 using System.Data;
4 using System.Data.Common;
5 using Microsoft.Practices.EnterpriseLibrary.Caching;
6 using Microsoft.Practices.EnterpriseLibrary.Caching.Expirations;
7 using Microsoft.Practices.EnterpriseLibrary.Data;
8
9 namespace WebApp
10 {
11 public partial class _Default : System.Web.UI.Page
12 {
13
14 string CacheKey="JIMMY_Message_SqlDependency_Cache";
15
16
17 protected void Page_Load(object sender, EventArgs e)
18 {
19 if (!IsPostBack)
20 {
21 this.Repeater1.DataSource = GetMessages("JIMMY");
22 this.Repeater1.DataBind();
23 }
24 }
25
26 /// <summary>
27 /// 从数据库实时查询指定用户的Message
28 /// </summary>
29 /// <param name="userName"></param>
30 /// <param name="sql"></param>
31 /// <returns></returns>
32 private DataTable GetMessageByUserId(string userName,string sql)
33 {
34 Database db = DatabaseFactory.CreateDatabase();
35 if (sql.Length==0)
36 {
37 sql = "Select [ID],[UserID],[Message] From [dbo].[Messages] Where [UserID]=@UserID";
38 }
39 DbCommand command = db.GetSqlStringCommand(sql);
40 db.AddInParameter(command, "UserID", DbType.String, userName);
41 return db.ExecuteDataSet(command).Tables[0];
42
43 }
44
45 /// <summary>
46 /// 获取用户的Message(缓存优先，数据库其次)
47 /// </summary>
48 /// <param name="userName"></param>
49 /// <returns></returns>
50 private DataTable GetMessages(string userName)
51 {
52 ICacheManager manager = CacheFactory.GetCacheManager();
53 if (manager.GetData(CacheKey) == null)
54 {
55 string sql = "Select [ID],[UserID],[Message] From [dbo].[Messages] Where [UserID]=@UserID";
56
57 Dictionary<string, object> Dic = new Dictionary<string, object>();
58 Dic.Add("UserID", userName);
59 manager.Add(CacheKey, GetMessageByUserId(userName, sql), CacheItemPriority.Normal, null, new SqlDependencyExpiration(sql,CommandType.Text, Dic));
60 }
61
62 return manager.GetData(CacheKey) as DataTable;
63 }
64 }
65 }
66
测试方法:
上面的示例中，缓存了[dbo].[Messages].[UserID]='JIMMY'的数据，如果首次打开页面时，直接从数据库中取数据，然后刷新一下，从Sql监视器中能看到此时并没有提交查询语句，即直接从缓存中读取数据。
然后在数据库中，直接UserID='JIMMY'的记录(比如修改Message字段值)，再次刷新页面，会发现重新向数据库提交了查询语句(即更新了缓存)，然后再次刷新，直接从缓存读取。
最后在数据库中，修改UserID<>'JIMMY'的记录，再次刷新页面，还是从缓存中数据(即修改UserID不为JIMMY的记录，不会触发SqlDependency的OnChange事件，缓存未过期)
源代码下载：http://files.cnblogs.com/yjmyzz/SqlDependency_Cache_Test.rar