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

出处:http://www.cnblogs.com/flaaash/archive/2007/12/11/991445.html

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);

上文的原始链接地址