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