关于数据库缓存依赖项的使用

      SqlCacheDependency基于表的缓存应用在sql2000,以及slq20005中实现的原

理多一样,多是在需要检测的表上建立触发器,并且Asp.net运行时通过轮询机制来检测表

数据是否更改.
  使用步骤
  首先保证数据库里有相应的基础结构,可以通过aspnet_regsql来配置,
  –S 服务器名称 –U 登陆ID –P 密码 –d 数据库名称 –t 要追踪的数据表的名称 –et
  eg：aspnet_regsql -S 192.168.14.250 -U sa -P 123456 -d AirTicket -t Plane -et
 或者通过代码来建立基本结构
 SqlCacheDependencyAdmin.EnableNotifications(string connStr);
 SqlCacheDependencyAdmin.EnableTableForNotifications(string connstr, string[] tables);
效果一样的

演示代码asp.net

=========基类==========

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Caching;

namespace Wapdm.Utility.CacheDependency
{
    public abstract class SqlTableCacheDependencyBase:ICacheDependency
    {
        protected AggregateCacheDependency _dependency = new AggregateCacheDependency();

        protected SqlTableCacheDependencyBase(string cacheDatabaseName, string tableName)
        {
            _dependency.Add(new SqlCacheDependency(cacheDatabaseName, tableName));
        }

        protected SqlTableCacheDependencyBase(string cacheDatabaseName, string[] tableNameList)
        {
            foreach (string tableName in tableNameList)
            {
                if (tableName != null && tableName.Length > 0)
                {
                    _dependency.Add(new SqlCacheDependency(cacheDatabaseName, tableName));
                }
            }
        }

        public AggregateCacheDependency GetDependency() {
            return _dependency;
        }
    }
}

 

=========子类===========

 

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;

namespace Wapdm.Utility.CacheDependency
{
    public class AirportNameTable : SqlTableCacheDependencyBase
    {
        public AirportNameTable() : base(ConfigurationSettings.AppSettings["CacheDataBase"], new string[]{ "AirPort", "Service_City", "ForeignArea" }) { }
    }
}

 

===========使用类=================================

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Caching;
using System.Web;
using System.Data;

namespace Wapdm.Utility.Caches
{
    public class CacheManager
    {
        readonly static string _airportName = "AirportName";

        public static string GetAirportName(string airCode)
        {
            string key = airCode;
            string paramValue = string.Empty;
            if (HttpContext.Current.Cache[key] == null)
            {
                if (HttpContext.Current.Cache[_airportName] == null)
                {
                    HttpContext.Current.Cache.Insert(_airportName,
                                                    Wapdm.Utility.Dal.DataOperation.GetAllAirportName(),
                                                    new Wapdm.Utility.CacheDependency.AirportNameTable().GetDependency());
                }
                DataSet ds = (DataSet)HttpContext.Current.Cache[_airportName];
                paramValue = Wapdm.Utility.Dal.DataOperation.GetAirportNameFromCache(key, ds.Tables[0]);
                HttpContext.Current.Cache.Insert(key, paramValue, new Wapdm.Utility.CacheDependency.AirportNameTable().GetDependency());
            }
            paramValue = HttpContext.Current.Cache[key].ToString();
            return paramValue;
        }
    }
}

 

============调用====================

string airPortName = CacheManager.GetAirportName(destinationairpor);

 

 

最后,基于表的数据库缓存必须在web.config文件中有如下配置
<system.web>
.....
  <caching>
   <sqlCacheDependency enabled="true" pollTime="10000">
    <databases>
     <add name="CacheTest" connectionStringName="SQL2005CacheTest" pollTime="10000"/>
    </databases>
   </sqlCacheDependency>
  </caching>
</system.web>
pollTime为论询时间,建议不要少于1分钟,或更长,不然建议不要使用此类缓存.
name="CacheTest",中的CacheTest,作为SqlCacheDependency(dbEntryName,table)构造函数参数dbEntryName,的传递值.这点要注意下.