ASP.NET2.0结合aspnet_regsql实现数据库的缓存依赖

ASP.NET2.0的数据库缓存依赖保证在表的内容发生改变后才使得缓存失效，能够保证缓存数据的及时刷新。根据我的实验，只要客户的重新编译，或者数据库表发生改变，都导致缓存失效。下面是具体的步骤。
1、启用表的缓存依赖，以Pubs数据库的Authors表为例：
//aspnet_regsql -S .\sqlexpress -E -d pubs -ed
//aspnet_regsql -S .\sqlexpress -E -d pubs -t authors -et
要想查看数据库现存的缓存依赖表，用下面的指令：
aspnet_regsql -S ."sqlexpress -E -d pubs -lt
2、在web.config文件里面做缓存以来配置，如下

  <connectionStrings>
    <add name="Pubs" connectionString="server=."sqlexpress; database = pubs; integrated security=true;"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

<system.web>
        <!--配置缓冲的连接池-->
      <caching>
        <sqlCacheDependency enabled="true" pollTime="1000">
          <databases>
            <add name="Pubs" connectionStringName="Pubs" pollTime="1000"/>
          </databases>
        </sqlCacheDependency>
      </caching>
</system.web>

3、编码实现缓存依赖的测试，如下：

        protected void Page_Load(object sender, EventArgs e)
        {
            // set SqlCacheDependency, it can only be related to one table in the database.
            //aspnet_regsql -S .\sqlexpress -E -d pubs -lt
            //注意下面的表必须和上面的输出大小写一致
            SqlCacheDependency dependency = new SqlCacheDependency("Pubs", "authors");
            // if cache is invalid, then regenerate dataset and insert into cache.
            if (Cache["DATA"] == null)
            {
                Cache.Insert("DATA", GetDataSet(), dependency);
                Response.Write("创建缓存！");
            }
            else
                Response.Write("读取缓存缓存！");

            GridView gvAuthors = new GridView();
            this.form1.Controls.Add(gvAuthors);
            gvAuthors.DataSource = (DataSet)Cache["DATA"];
            gvAuthors.DataBind();
        }

        // Generate dataset
        public DataSet GetDataSet()
        {
            SqlConnection connection = new SqlConnection(@"data source=."sqlexpress;initial catalog=Pubs;Integrated Security=True");
            DataSet ds = new DataSet();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "select * from authors";
            SqlDataAdapter sa = new SqlDataAdapter();
            sa.SelectCommand = command;
            sa.Fill(ds, "Employees");
            return ds;
        }

以上为我测试的代码，相关参考资料如下：

---

aspnet_regsql.exe -?  //help
aspnet_regsql.exe -S . -d Northwind -E -ed  // Enable the database for sql cache dependency

// enable table for sql cache dependency
aspnet_regsql.exe -S . -d Northwind -E -t Employees -et
aspnet_regsql.exe -S . -d Northwind -E -t Customers -et

aspnet_regsql.exe -S . -d Northwind -E -lt  // list all the table enable sql cache dependency

1.Use the aspnet_regsql.exe above to create a table and triggers for the sql cache dependency.
2.Several ways of Caching.
a. if you want to cache the page,you have to specify the sentence like this at the beginning of the page.
   <%@ OutputCache Duration="3600" SqlDependency="Northwind:Employees" VaryByParam="none" %>
b. if you use SqlDatasource, don't forget to add SqlCacheDependency="Northwind:Employees" to this control.
 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyNewNorthwindConnectionString %>" SqlCacheDependency="Northwind:Employees"
            EnableCaching="True" SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate] FROM [Employees]">
        </asp:SqlDataSource>
c.Use Caching Programmatically
    protected void Page_Load(object sender, EventArgs e)
    {
        // set SqlCacheDependency, it can only be related to one table in the database.
        SqlCacheDependency dependency = new SqlCacheDependency("Northwind", "Employees");
 // if cache is invalid, then regenerate dataset and insert into cache.
        if (Cache["DATA"] == null)
            Cache.Insert("DATA", GetDataSet(),  dependency);
      
        GridView1.DataSource = (DataSet)Cache["DATA"];
        GridView1.DataBind();
    }

    // Generate dataset
    public DataSet GetDataSet()
    {
        SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
        DataSet ds = new DataSet();
        SqlCommand command = connection.CreateCommand();
        command.CommandText = "select top 10 lastname,firstname,companyName from customers,employees";
        SqlDataAdapter sa = new SqlDataAdapter();
        sa.SelectCommand = command;
        sa.Fill(ds,"Employees");
        return ds;
    }

Additional, if you want the data to be related to more than one table. You have to modify the trigger in the database or Use AggregateCacheDependency Class as follows,

string cacheDBTables = ConfigurationManager.AppSettings["CacheTabularTable"].ToString();
AggregateCacheDependency dependencies = new AggregateCacheDependency();
string[] tables = cacheDBTables.Split(',');
foreach (string tableName in tables)
    dependencies.Add(new SqlCacheDependency(clsConstants.Constant_Cache_Database, tableName));
HttpContext.Current.Cache.Insert(clsConstants.Constant_Cache_ForecastTabularData, ds, dependencies);

上文的原始链接地址