SqlDependency和SqlCacheDependency缓存的用法及具体步骤

• SqlDependency是outputcache网页缓存的一个参数，它的作用是指定缓存失效的数据库依赖项，可以具体到数据库和表。

  SqlDependency能解决什么问题？

  Asp.Net中的cache可以设置一个过期时间，但设置多久合适呢？长了浪费，短了就失去缓存的意义了。使用SqlDependency进行缓存则可以解决这个问题。

  SqlDependency是.net2.0封装的一个类型，要配合sql2005或以上版本才能使用。

  另外，SqlDependency类需要数据库的ServiceBroker来支持，当数据库中的数据发生变化时通知应用程序更新缓存，这才是最有效的缓存方式。

  SqlDependency配合ServiceBroker实现缓存

  步骤一：

  sql数据库必须开启ServiceBroker服务，首先检测是否已经启用ServiceBroker，检测方法：

  Select  DATABASEpRoPERTYEX('数据库名称','IsBrokerEnabled')

  --1表示已经启用0表示没有启用

  步骤二：

  如果ServiceBroker没有启用，使用下面语句启用：

  ALTER  DATABASE  <数据库名称>  SET  ENABLE_BROKER;

  步骤三：

  在实现基于服务的SQL数据缓存依赖过程中，需要显式调用SqlDependency.Start来启动接受依赖项更改通知的侦听器。

  SqlDependency.Start(connectionString);//推荐将这段代码加到Global.asax的Application_Start方法中 
  SqlDependency.Stop(connectionString);//用于关闭，可加在Global.asax的Application_End方法中

  步骤四：缓存实现

  使用sqldependency实现缓存的代码：

  public class CacheHelper { 
      static Cache WebCache = HttpContext.Current.Cache; 
      static string DefaultConn = ConfigurationManager.ConnectionStrings["Default"].ConnectionString; 
      public static DataTable GetSystemParams() { 
        if (WebCache["SystemParam"] == null) { 
              string strSQL = "select uSystemParamID,ParamName,ParamValue,Description from  dbo.DTS_SystemParam"; 
          SqlDataAdapter da = new SqlDataAdapter(strSQL, DefaultConn); 
          SqlDependency dep = new SqlDependency(da.SelectCommand); 
          dep.OnChange += new OnChangeEventHandler(dep_OnChange);
          DataTable tbl = new DataTable(); da.Fill(tbl); 
          WebCache["SystemParam"] = tbl; 
          return tbl;
         }
         else { 
            return (DataTable) WebCache["SystemParam"]; 
         } 
  }
     private static void dep_OnChange(object sender, SqlNotificationEventArgs e) {
          WebCache.Remove("SystemParam"); 
     }
  }
   

  注意：

  使用 SqlDependency 订阅查询通知必须向SQL Server Service Broker提供制定规则的查询语句，一般来讲，必须是简单的sql查询语句（不能用*，不能用top，不能用函数，包括聚合函数，不能用子查询，包括where后的子查询，不能用外连接，自连接，不能用临时表，不能用变量，不能用视图，不能垮库，表名之前必须加类似dbo数据库所有者这样的前缀）例如：select * from table1，select column1 from table1，select count(*) from table1 都是错误的sql查询语句，select column1 from dbo.table1 则是正确的语句。

  有关SqlDependency 的用法，还可以参考如下步骤的实现方式：

  1.  注册连接池

  命令：aspnet_regsql -S 192.168.30.220\SQL2K -U sa -P sa -ed -d pubs -et -t test专门注册连接池的工具 在sql数据库的数据 库改变了，才改变缓存。

  参数：

  _s 制定注册的服务器
  _u和 _p 说明是sql数据库的授权模式、_d 指定数据库的名字
  _ed 说明缓存生效。

  示例：

  aspnet_regsql -S 192.168.30.220\SQL2K -U sa -P sa -ed -d pubs -et -t test

  进行outputcache配置

  <%@ OutputCache SqlDependency="pubs:test" Duration="100" VaryByParam="id"%>

  2. 设置WebConfig      

  <connectionStrings>
    <add  name="mySqlServer" connectionString="Server=192.168.30.220\SQL2K;Database=pubs;uid=sa;pwd=sa;"/>  
  </connectionStrings>
  <caching>
   <sqlCacheDependency enabled="true">
     <databases>
        <add
            connectionStringName="mySqlServer"
            pollTime="500" />
     </databases>
   </sqlCacheDependency>
  </caching>

  name：必须是数据库的名字

  connectionStringName：连接字符串的名称

  除了可以建立数据库依赖，还可以建立文件依赖或者其他依赖。

  SqlCacheDependency的用法

  SqlCacheDependency的使用需经过几个具体的设置步骤：

  该实例中，SqlCacheDependency缓存依赖该数据表，即如果表中数据发生了变化，缓存应该失效。

  1. 修改web.config，启用SqlCacheDependency。将下列代码加入web.config的<system.web>节：

  <caching>
      <sqlCacheDependency enabled="true">
          <databases>
              <add connectionStringName="regex_libConnectionString" name="IPBlockedDependency"/>
          </databases>
      </sqlCacheDependency>
  </caching>

  这里的connectionStringName指定了在<connectionStrings>中添加的某一个连接字符串。name则是为该SqlCacheDependency起的名字，这个名字将在第3步中用到。

  2. 执行下述命令，为数据库启用缓存依赖：

  C:\Program Files\Microsoft Visual Studio 9.0\VC>aspnet_regsql -C "Data Source=.;Initial Catalog=regex-lib;Integrated Security=True" -ed -et -t "IPBlocked"这里-C后面的字符串是连接字符串（请替换成自己所需要的值），-t参数后面的字符串是数据表的名字。命令执行后，数据库中会多出一个AspNet_SqlCacheTablesForChangeNotification表。

  3. 在代码中使用缓存，并为其设置SqlCacheDependency依赖：

  private static string[] GetBlockedIPs()
  {
      // 1尝试从缓存中读取
      string[] ips = (string[])HttpContext.Current.Cache[BlockedIPCacheKey];
      if (ips != null)
          return ips;
      // 2从数据库中读取
      using (RxDataContext db = new RxDataContext())
      {
          ips = db.IPBlockeds.Select(ipb => ipb.UserIP).ToArray();
      }
      // 3放入缓存
      SqlCacheDependency depend = new SqlCacheDependency("IPBlockedDependency", "IPBlocked");
      HttpContext.Current.Cache.Insert(BlockedIPCacheKey, ips, depend);
      return ips;
  }

  创建SqlCacheDependency时需要指定web.config中定义的SqlCacheDependency名字，并指定数据表的名称。