Caching in ASP.NET pages

The simplest situation in caching is when you need to cache a page for a specific number of seconds regardless of the page parameters. The code below does the magic for 5 seconds:

<%@ OutputCache Duration="5" VaryByParam="*" %>

If you show data from a database, for example SQL Server, this method will not display the last version of the data. If a new row is added to the database within the amount of time specified as Duration, it will not be shown until the specified time elapses. To make the page display the latest version of data, you need a SqlCacheDependency object. There are two methods to perform this job. The first one is polling and is supported by both SQL Server 2000 and SQL Server 2005. The second one is using broker service which is supported in SQL Server 2005.

To enable the first method on a SQL Server database, you need to run aspnet_regsql command with a few switches indicating the target database and tables for caching.

C:\>aspnet_regsql –S myServer –U myUsername –P myPassword –d myDatabase –ed –et –t myTable

This command adds a table named ‘AspNet_SqlCacheTablesForChangeNotification’ and a trigger named ‘myTable_AspNet_SqlCacheNotification_Trigger’ to the database. The table is going to be polled by ASP.NET to see if there is any change.
 Also, you need to add the code below in the web.config file:

<caching>
      <sqlCacheDependency enabled="true" pollTime="2000">
            <databases>
                  <add connectionStringName="myDBCS" name="myDBCS"/>
            </databases>
      </sqlCacheDependency>
</caching>

Here I assume that we have a connection string defined as myDBCS. Also the OutputCache directive will need an additional attribute:

<%@ OutputCache Duration="5" VaryByParam="*" SqlDependency="myDBCS:myTable"%>

To enable the second method, you should change the code above to this:

<%@ OutputCache Duration="5" VaryByParam="*" SqlDependency="CommandNotification"%>

Also, you need to add a Global.asax file to the application and add the code snippet below to start SQL dependency:

System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["myDBCS"].ConnectionString);

Note: It is very important to know that you must include the database schema in the select query supplied in the SqlDataSource object you use to let the broker service work properly.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings: myDBCS %>"
SelectCommand="SELECT column1, column2 from dbo.table1">
</asp:SqlDataSource>