Introduction
This type of SQL cacheing invalidation is working only with SQL server 7 or above.
Caching has been improved in ASP.NET 2.0. The most interesting feature is the introduction of database-triggered cache invalidation.
Three ways to do this SQL based invalidation.
1. Declarative Output caching by using the OutputCache directive.
2. Programmatic Output caching by using SqlCacheDependency object.
3. Cache API
In old framework 1.x there was polling mechanism for checking invalidity of data, but in Framework 2.0 and SQL server 2005 this process is completely reverse, means now sql server 2005 will notify asp pages about change in data bu using IIS posrt 80 by sending HTTP request.
You can configure SQL Server 2005 to notify your ASP.NET application whenever changes have been made to a database, a database table, or a database row.
To configure SQL server for cacheing we can use SqlCacheDependencyAdmin class,
The SqlCacheDependencyAdmin class has five important methods:
- DisableNotifications—Disables SQL Cache Invalidation for a particular database.
- DisableTableForNotifications—Disables SQL Cache Invalidation for a particular table in a database.
- EnableNotifications—Enables SQL Cache Invalidation for a particular database.
- EnableTableForNotifications—Enables SQL Cache Invalidation for a particular table in a database.
- GetTablesEnabledForNotifications—Returns a list of all tables enabled for SQL Cache Invalidation.
In ASP.NET 2.0 we can also create custome cache dependency.
Using the code
<configuration>
<connectionStrings>
<add name="mySqlServer"
connectionString="Server=localhost;Database=Pubs" />
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add
name="Pubs"
connectionStringName="mySqlServer"
pollTime="60000" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>
Using SQL Cache Invalidation with Page Output Caching
<%@ OutputCache SqlDependency="Pubs:Titles"
Duration="6000" VaryByParam="none" %>
<html>
<head runat="server">
<title>Output Cache Titles</title>
</head>
<body>
<form id="form1" runat="server">
<%= DateTime.Now %>
<asp:GridView
ID="grdTitles"
DataSourceID="SqlDataSource1"
Runat="Server" />
<asp:SqlDataSource
ID="SqlDataSource1"
SelectCommand="Select * FROM Titles"
ConnectionString="<%$ ConnectionStrings:mySqlServer %>"
Runat="Server" />
</form>
</body>
</html>
Using SQL Cache Invalidation with the DataSource Control
<html>
<head id="Head1" runat="server">
<title>SqlDataSource Caching</title>
</head>
<body>
<form id="form1" runat="server">
<%= DateTime.Now %>
<asp:GridView
ID="grdTitles"
DataSourceId="SqlDataSource1"
Runat="server" />
<asp:SqlDataSource
ID="SqlDataSource1"
EnableCaching="true"
SqlCacheDependency="Pubs:Titles"
SelectCommand="select * from titles"
ConnectionString="<%$ ConnectionStrings:mySqlServer %>"
Runat="server" />
</form>
</body>
</html>
Caching has a dramatic impact on the performance of database-driven Web applications. Fortunately, the ASP.NET 2.0 framework includes a number of significant new enhancements that make it easier to take advantage of caching in your applications.
The new DataSource controls include properties that make it easy to cache database data in memory. By taking advantage of the DataSource controls, you can retrieve database data and cache the data without writing a single line of code.
Points of Interest
I like to Share my knowledge with guys like you, because i am also one of you, that if i dont know anything then i come to you...so keep exchange of knowledge...
History
keep attached with my simple way series articles....
Chirag Patel, a Programmer Analyst in a well known IT company working on .NET Technologies since last 2 years. He is interested in Pure business logic and fuzzy logic. his area of interest is in C#.NET, VB.NET and MSSQL 2005.
catch me on: http://groups.google.com/group/codewar