出處: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>
$False$
n><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(<
span >"讀取緩存緩存！");
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&nbs
p;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"
0"> 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
<span >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);上文的原始鏈接地址
