配置文件:
<configuration> <connectionStrings> <add name="SqlCacheConn" connectionString="Server=192.168.1.5;Packet Size=32768;Database=test;uid=sa;password=123456;max pool size=512; Integrated Security=false;" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="SqlCache" connectionStringName="SqlCacheConn"/> </databases> </sqlCacheDependency> </caching> </system.web> </configuration>
启用消息通知:
//用命令启用SQL缓存通知 C:\Windows\Microsoft.NET\Framework\v4.0.30319
aspnet_regsql.exe -S localhost -U sa -P password -ed -d 数据库名字 -et -t 表名
启用后数据库相应的变化:
AspNet_SqlCachePollingStoredProcedure AspNet_SqlCacheQueryRegisteredTablesStoredProcedure AspNet_SqlCacheRegisterTableStoredProcedure AspNet_SqlCacheUnRegisterTableStoredProcedure AspNet_SqlCacheUpdateChangeIdStoredProcedure AspNet_SqlCacheTablesForChangeNotification [表名_AspNet_SqlCacheNotification_Trigger]
代码实现
/// <summary> /// 设置缓存 /// </summary> private object SetCache() { //自定义的数据操作类 var dal = new InterfaceCallDal.DALProvider(appversion); //创建数据集 这里可能有点多此一举哈。。 dal.CreateDataSet("table_test"); DataSet ds = dal.ProviderData; //根据连接字符串和表名通知该数据库的表实行缓存依赖通知 //EnableTableForNotifications是静态方法,有两个重载方法 //第一个参数为数据库的连接字符串,第二个参数为缓存所依赖的数据库表名 SqlCacheDependencyAdmin.EnableTableForNotifications(dal.ConStr, "table_test"); //通过web配置文件读取ZitOCS需要的连接字符串,并指明数据集中与缓存依赖的表 //第一个参数为web节点指定链接字符串,第二个参数为数据集中与缓存依赖的DataTable var sqlCache = new SqlCacheDependency("SqlCache", "table_test"); //插入缓存。第一个参数为缓存名称,第二个为数据源,第三个为依赖实例 HttpRuntime.Cache.Insert(cacheKey, ds, sqlCache); return HttpRuntime.Cache.Get(cacheKey); }
/// <summary> /// 一个数据提供类 /// </summary> public class DALProvider { //默认查询字符串 public DALProvider(string appversion) { _sqlStr = string.Format("select name,enabled from table_test(nolock) where version='{0}' and app_type='app'", appversion); } private DataSet ds = new DataSet(); private readonly string _conStr = ConfigHelper.SqlCacheConn; public string ConStr { get { return _conStr; } } private readonly string _sqlStr; public DataSet ProviderData { get { return ds.Tables.Count == 0 ? null : ds; } } //创建数据集 public void CreateDataSet(string tableName) { var da = new SqlDataAdapter(_sqlStr, _conStr); da.Fill(ds, tableName); } }