数据库缓存

by ·数据库缓存已关闭评论

SqlDependency类有一OnChangeEventHandler方法，可注册委托，当接收到改变时，用委托实施数据更改自动通知前台
例：http://www.360doc.com/content/09/0609/16/32573_3830694.shtml
用此方法可实现删除过期的缓存。要注意的是command 指定的 commandText 的查询结果发生变化时触发 OnChange，且只触发一次，故需要在触发事件中再次建立依赖和绑定接受通知.sqlDependency start后，会开户一个线程等待，当通知由服务器的service Broker传来的时候，线程收到信息，调用回调函数

sqlDependency的start与stop都是控制线程的开关，注意此线程是全局性的，共享。

不过.net同时有SqlCacheDependency，可自动完成删除缓存的操作。

相关sql:

–订阅
–在做出通知后，会进行删除，所以需要重新订阅
 
select * from sys.dm_qn_subscriptions
 
–信息对接
SELECT TOP 50 * 
FROM sys.transmission_queue

–关掉订阅
KILL QUERY NOTIFICATION SUBSCRIPTION  10

要达到的效果：
将数据库中的数据放到缓存中，当数据变化时，自动通过应用删除缓存
Sql server 7.0/2000下 SqlCacheDependency使用轮询的方式进行缓存失效检查，
Sql server 2005/2008下增加使用查询通知方式进行缓存失效检查。

实施：
参考：http://space.itpub.net/16436858/viewspace-630489
http://www.cnblogs.com/over140/archive/2009/01/15/1376318.html

1.检测是否已经启用Service Broker

  Select DATABASEpRoPERTYEX('数据库名称','IsBrokerEnabled')  — 1 表示已经启用 0 表示没有启用

2.启用Service Broker                   

  ALTER DATABASE 数据库名称 SET ENABLE_BROKER;              

 –WITH ROLLBACK IMMEDIATE;
GO
 

3.给您的数据库访问帐号授予权限

  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO test

  注意：这一步非常重要, 如果没有权限, 数据库改变的通知将无法接收, cache永远都不会被刷新，注意 sa授此权限(ms禁止)， 所以，换个数据库访问帐号即可.
或者用：alter authorization on database::[<your_SSB_DB>] to [sa];
4，缓存代码

/// <summary>
    /// 设置表依赖缓存
    /// </summary>
    public static DataTable GetTableCache(string TableName, string CacheDataBase,string ConnStr)
    {
        System.Web.Caching.Cache objCache = HttpRuntime.Cache;
        DataTable dt = new DataTable();
        //objCache.Insert(CacheKey, objObject);
        if (objCache[TableName] == null)
        {
            try
            {
                //get from database
                DataSet ds = new DataSet();
 
                SqlDependency.Start(ConnStr);//启动与关闭放在Global的Application_Start,End中好一些
 
                using (SqlConnection connection = new SqlConnection(ConnStr))
                {
                    //只有sql中指定的字段发生更改时才会提醒。
                    string sql = string.Format(" SELECT ID,ParentID,Name,OrderBy,OrganID FROM dbo.mdCity");
 
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        SqlCacheDependency dependency = new SqlCacheDependency(command);
 
                        //using (SqlDataAdapter adapter = new SqlDataAdapter()) //查询数据
                        //{
                        //    adapter.SelectCommand = command;
                        //    adapter.Fill(ds);
                        //}
 
                        //HttpRuntime.Cache.Insert("EntityResourceCollection", ds, dependency);
                        connection.Open();
                        dt.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                        objCache.Insert(TableName, dt, dependency);
                    }
 
                }
 
 
            }
            catch
            {
 
            }
 
        }
        else
        {
            dt = (DataTable)objCache[TableName];
        }
 
        return dt;
    }

5,注意 只有sql中指定字段发生更改时才会提醒。

关于SqlDependency类，它的限制很多，特别要注意的就是command的sql语句问题:
select id,name from dbo.test where id<>4 order by id desc 
他只能支持上面这样的简单语句
表名前需要架构名，如dbo

sql注意

满足下列要求的 SELECT 语句支持查询通知：

  • 必须显式说明 SELECT 语句中提取的列，并且表名必须限定为两部分组成的名称。注意，这意味着语句中引用的所有表都必须处于同一数据库中。
  • 语句不能使用星号 (*) 或 table_name.* 语法指定列。
  • 语句不能使用未命名列或重复的列名。
  • 语句必须引用基表。
  • 语句不能引用具有计算列的表。
  • 在 SELECT 语句中提取的列不能包含聚合表达式，除非语句使用 GROUP BY 表达式。提供 GROUP BY 表达式时，选择列表便可以包含聚合函数 COUNT_BIG() 或 SUM()。但是，不能为可为空的列指定 SUM()。语句不能指定 HAVING、CUBE 或 ROLLUP。
  • 在用作简单表达式的 SELECT 语句中提取的列不能多次显示。
  • 语句不能包含 PIVOT 或 UNPIVOT 运算符。
  • 语句不能包含 UNION、INTERSECT 或 EXCEPT 运算符。
  • 语句不能引用视图。
  • 语句不能包含下列任意一个：DISTINCT、COMPUTE、COMPUTE BY 或 INTO。
  • 语句不能引用服务器全局变量 (@@variable_name)。
  • 语句不能引用派生表、临时表或表变量。
  • 语句不能从其他数据库或服务器中引用表或视图。
  • 语句不能包含子查询、外部联接或自联接。
  • 语句不能引用下列大型对象类型：text、ntext 和 image。
  • 语句不能使用 CONTAINS 或 FREETEXT 全文谓词。
  • 语句不能使用行集函数，包括 OPENROWSET 和 OPENQUERY。
  • 语句不能使用下列任何一个聚合函数：AVG、COUNT(*)、MAX、MIN、STDEV、STDEVP、VAR 或 VARP。
  • 语句不能使用任何具有不确定性的函数，包括排名函数和窗口函数。
  • 语句不能包含用户定义聚合。
  • 语句不能引用系统表或视图，包括目录视图和动态管理视图。
  • 语句不能包含 FOR BROWSE 信息。
  • 语句不能引用队列。
  • 语句不能包含无法更改和无法返回结果的条件语句（如 WHERE 1=0）。
  • 语句不能指定 READPAST 锁提示。
  • 语句不能引用任何 Service Broker QUEUE。
  • 语句不能引用同义词。
  • 语句不能具有基于 double/real 数据类型的比较或表达式。