.net SQL依赖缓存

项目说明：

页面缓存：PageCache.aspx

文件依赖缓存：myCacheDependency.aspx
SQL缓存依赖：mySqlCacheDependency.aspx

接口类库：ICacheDependency
实现类库：TableCacheDependency
工厂类库：CacheDependencyFactory

运行结果：在mySQLCacheDependency.aspx页面点击button显示在Lable上的时间值是不变的，因为时间值来自于缓存，但当你对FelixProject的两个表：T_News和T_Links的内容更进行改动后，再点击button，Lable上的时间值就会被更新，证明缓存成功的依赖了数据库中指定的表。

首先在cmd命令窗口中用aspnet_regsql.exe创建SQL依赖缓存表，命令如下：

C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -S 192.168.3.33
-U temp -P temp -d FelixProject -ed

Enabling the database for SQL cache dependency.

................................................................

Finished.

C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -S 192.168.3.33
-U temp -P temp -t T_News -et

The database is missing from the arguments.

C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -S 192.168.3.33
-U temp -P temp -d FelixProject -t T_News -et

Enabling the table for SQL cache dependency.

................................................................

Finished.

C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -S 192.168.3.33
-U temp -P temp -d FelixProject -t T_Links -et

Enabling the table for SQL cache dependency.

下面是各类库和页面的代码：

ICacheDependency.ICacheDependency.cs

----------------------------

using System;
using System.Web.Caching;

namespace ICacheDependency
{
    public interface ICacheDependency
    {
        AggregateCacheDependency GetDependency();
    }
}

TableCacheDependency.TableCacheDependency.cs
------------------------

using System;
using System.Web.Caching;
using System.Configuration;

namespace TableCacheDependency
{
    public class TableCacheDependency : ICacheDependency.ICacheDependency
    {
        public AggregateCacheDependency GetDependency()
        {
            AggregateCacheDependency dep = new AggregateCacheDependency();
            //从web.config中获取dbName值
            string dbName = ConfigurationSettings.AppSettings["dbName"];
            //从web.config中获取tbStrs值
            string tbStrs = ConfigurationSettings.AppSettings["tbStrs"];
            string[] tbs = tbStrs.Split('|');
            //循环数组，创建SqlCacheDependency, 并添加到dep
            foreach (string tbName in tbs)
            {
                dep.Add(new SqlCacheDependency(dbName, tbName));
            }
            return dep;
        }
    }
}

CacheDependencyFactory.Access.cs

-------------------------------

using System.Reflection;
using System.Configuration;

namespace CacheDependencyFactory
{
    public class Access
    {
        private static readonly string path = ConfigurationSettings.AppSettings["Cache"];
        public static ICacheDependency.ICacheDependency CreateDependency()
        {
            string classname = path + ".TableCacheDependency";
            return (ICacheDependency.ICacheDependency)Assembly.Load(path).CreateInstance(classname);
        }

}
}

CacheDependencyFactory.Facade.cs

-------------------------------

using System.Web.Caching;

namespace CacheDependencyFactory
{
    public class Facade
    {
        public static AggregateCacheDependency GetDependency()
        {
            //TableCacheDependency.TableCacheDependency tbcachedep = new TableCacheDependency.TableCacheDependency();
            //return tbcachedep.GetDependency();

            return Access.CreateDependency().GetDependency();
        }
    }
}

D:\websites\web\mySQLCacheDependency.aspx
--------------------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="mySQLCacheDependency.aspx.cs" Inherits="mySQLCacheDependency" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>SQL缓存依赖</title>

</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

D:\websites\web\mySQLCacheDependency.aspx.cs
--------------------------------

using System;
using System.Web;
using System.Web.Caching;

public partial class mySQLCacheDependency : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ShowTime();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        ShowTime();
    }
    protected void ShowTime()
    {
        string timestr = Convert.ToString(HttpRuntime.Cache["TimeNow"]);
        if (String.IsNullOrEmpty(timestr))
        {
            //如果获取失败，重载当前时间
            timestr = DateTime.Now.ToString();

//新建缓存依赖
AggregateCacheDependency dep = CacheDependencyFactory.Facade.GetDependency();

            //插入缓存
            Cache.Insert("TimeNow", timestr,dep);
        }
        //显示时间
        Label1.Text = timestr;
    }
}

D:\websites\web\Web.config

---------------------

<?xml version="1.0"?>

<configuration>
<appSettings>
    
    <add key="dbName" value="FelixProject"/>
    <add key="tbStrs" value="T_News|T_Links"/>
    <add key="Cache" value="TableCacheDependency"/>
</appSettings>
<connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
    
    <add name="FelixProjectConnectionString" connectionString="Data Source=192.168.3.33;Initial Catalog=FelixProject;Persist Security Info=True;User ID=temp;Password=temp" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
    <compilation debug="true" targetFramework="4.0"/>
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login.aspx" timeout="2880"/>
    </authentication>
    <membership>
      <providers>
        <clear/>
        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/"/>
      </providers>
    </membership>
    <profile>
      <providers>
        <clear/>
        <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/"/>
      </providers>
    </profile>
    <roleManager enabled="false">
      <providers>
        <clear/>
        <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/"/>
        <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/"/>
      </providers>
    </roleManager>
    <caching>
      
      <outputCache enableOutputCache="true"/>
      <outputCacheSettings>
        <outputCacheProfiles>
          <add name="myPageCache" duration="10" enabled="true"/>
        </outputCacheProfiles>
      </outputCacheSettings>
      
      <sqlCacheDependency enabled="true" pollTime="3000">
        <databases>
          <add name="FelixProject" connectionStringName="FelixProjectConnectionString"/>
        </databases>
      </sqlCacheDependency>
    </caching>
</system.web>
<system.webServer>
    <modules runAllManagedModulesForAllRequests="true"/>
</system.webServer>
</configuration>