本文是在Artech“[原创]Enterprise Library深入解析与灵活应用(2): 通过SqlDependency实现Cache和Database的同步”的基础之上,将其示例移植到webform环境中而已,详细原理还请大家见Artech的文章
应用场景:利用Enlib4.1的缓存模块,实现常用数据的缓存,同时借助SqlDependency通过"监控数据是否有改动"来决定缓存是不是过期。
1.测试数据表及数据(sql2005环境)
![img_1c53668bcee393edac0d7b3b3daff1ae.gif](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif)
![img_405b18b4b6584ae338e0f6ecaf736533.gif](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif)
1
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
2
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
3
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
4
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
5
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
6
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
7
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
8
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
9
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
10
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
11
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
12
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
13
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
14
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
15
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
16
![img_a6339ee3e57d1d52bc7d02b338e15a60.gif](https://yqfile.alicdn.com/img_a6339ee3e57d1d52bc7d02b338e15a60.gif)
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