[转]使用基于轮询的SQL数据缓存依赖

使用基于轮询的SQL数据缓存依赖,主要有三个过程:

一.配置数据库

配置数据库可以使用aspnet_regsql命令,也可以使用SqlCacheDependencyAdmin类.这里我们选择用SqlCacheDependencyAdmin类..具体做法为:在Global.asax的Application_OnStart中用SqlCacheDependencyAdmin类启用相关数据库和数据表的缓存依赖.示例代码如下:

<% @ Application Language = " C# "   %>
<% @ Import Namespace = " System.Configuration "   %>
<% @ Import Namespace = " System.Web.Caching "   %>

< script runat = " server " >

     void  Application_Start( object  sender, EventArgs e) 
     {
        //---以下实现的是启动数据库和数据表的SQL缓存依赖功能,登录用户需要有管理相关数据库的权限

       //获取连接字符串
        string connString = ConfigurationManager.ConnectionStrings["pubsConn"].ConnectionString;
        //变量needToInstall指示是否需要启动数据缓存依赖
        bool needToInstall = true;
        try
        {
            string[] tables = SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connString);
            if (tables != null)
            {
                //如果包含titles表,则变量为needToInstall为false,即不用再安装
                foreach (string table in tables)
                {
                    if (table.ToLower().Equals("titles"))
                    {
                        needToInstall = false;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            needToInstall = true;
        }
        //---如果变量needToInstall为true,则启用数据库pubs和数据表titles的缓存依赖功能
        if (needToInstall)
        {
            try
            {
                //启用数据库缓存依赖
                SqlCacheDependencyAdmin.EnableNotifications(connString);
                //启用数据表缓存依赖
                SqlCacheDependencyAdmin.EnableTableForNotifications(connString, "titles");
            }
            catch (Exception ex)
            {                 
            }
        }

    }

..

</ script >

二.配置Web.config文件.

另外,还需要配置Web.config文件,以设置连接字符串及缓存设置:

<? xml version = " 1.0 "  encoding = " utf-8 " ?>

< configuration >

   < connectionStrings > // 设置连接字符串
     < add name = " pubsConn "  connectionString = " Data Source=localhost;Initial Catalog=pubs;IntegratedSecurity=True; "  providerName = " System.Data.SqlClient " />     
   </ connectionStrings >
  
     < system.web >
      
       < caching > // 设置缓存依赖
         < sqlCacheDependency enabled = " true "  pollTime = " 600 " >
           < databases >
             < add name = " pubs "  connectionStringName = " pubsConn " />
           </ databases >
         </ sqlCacheDependency >
       </ caching >   
      ..
   </ system.web >
</ configuration >

三.开始使用.

开启了数据库的缓存依赖功能,并在Web.config中配置了缓存属性后,便可开始使用该缓存依赖.

可以在页面缓存中应用该缓存依赖:

<% @ OutputCache Duration = " 100 "  SqlDependency = " pubs:titles "  VaryByParam = " none "   %>

也可以在数据库源中运用该缓存依赖:

< asp:SqlDataSource ID = " SqlDataSource1 "  runat = " server "  EnableCaching = " true "  SqlCacheDependency = " pubs:titles "  SelectCommand = " select titledid,price from dbo.titles "  ConnectionString = " <%$ ConnectionStrings:connString %> "   />

当然,也可以在应用程序数据缓存中使用该缓存信赖:

SqlCacheDependency scd = new  SqlCacheDependency( " pubs " , " titles " );

Cache.Insert( " TitleCount " ,count,scd);