ASP.Net - How to make use of Caching?

How to make use of Caching?

Step 1: Basic sample with date output.

1. Create a new web project.

2. Add <%Response.Write(System.DateTime.Now); %> between div tags

Add this special directive after the page directive

<%@ OutputCache Duration="20" VaryByParam="*" %>.

The cache duration here is 20 seconds. VarByParam=”*” applies the caching to every page irrespective of the query string that is passed in. Now if you open the page in the browser, you will notice that the page doesn’t get updated if you refresh within 20 seconds of the last refresh. This is page level output caching.

Step 2 : Let’s take it a step higher by connecting our system to the database and use Database Caching.

1. Add the connection string to web.config file

<configuration>

<connectionStrings>

<add name="NW"

connectionString="data source=devserver;Initial Catalog=devdb;User ID=sa;Password=hello" providerName="System.Data.SqlClient" />

connectionStrings>

2. Add this to the webForm1.aspx page

<div>

<%Response.Write(System.DateTime.Now); %>

<asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ConnectionStrings:NW %>" SelectCommand="Select * from dbo.Customer" />

<asp:DataGrid ID="dg1" runat="server" DataSourceID="ds1" />

div>

Now if you refresh the webpage, it displays the table and it does not make a query back to the database within the 20 seconds of the last refresh. You can see this by opening sql profiler and enabling it. You will notice in the sql profiler that there is no database traffic going back to the system. You have to wait for 20secs to elapse and there is our query in sql profiler. This is a great simple way of doing data caching. However there is a problem. If the data changes on the back end, you would want the cached version of this page thrown away and refreshed immediately. Now that has been something that has been difficult to do in the past. Now it is easily supported with asp.net 2.0 and higher.

Step 3: To do this we have to enable database support to table dependency caching. So there is a command line tool called aspnet_regsql you could use to enable table cache to include customer table in the ibddevdb database. So execute the following command

Aspnet_regsql –S devserver –U sa –d devdb –ed –et –t Customer

The command prompts for password. Enter the password and you can see in the sql profiler that it did some stuff on the backend.

Now open the Microsoft Sql Server management studio and open the tables under devdb database and open the tables. Now you will notice that there is an extra table AspNet_SqlCacheTablesForChangeNotification. Expand the Customer table, you will notice that there is a trigger Customer_AspNet_SqlCacheNotification_Trigger. Every time there is a change to the Customer table, it writes a row to the table AspNet_SqlCacheTablesForChangeNotification. This table is going to be polled by asp.net to see if there are any changes to the system.

Now that we have set it up for the database, we need to set it up for our website.

Step 4:

1. To do this, we need to make an entry in the web.config file

<system.web>

<caching>

<sqlCacheDependency enabled="true" pollTime="2000">

<databases>

<add connectionStringName="NW" name="NW"/>

databases>

sqlCacheDependency>

The polling time is set to 2000 milli seconds.

2. Go back to the default page and add SqlDependency attribute to the OutputCache directive.

<%@ OutputCache Duration="20" VaryByParam="*" SqlDependency="NW:Customer"%>

3. Now if we go back and hit refresh, we see we have the table. If we go back to the sql server profiler, we see that the polling is happening every couple of seconds. If we go change a record in the table, and we go hit refresh, it shows right away.

4. This is great because it works both for Sql server 2000 and 2005.But Sql server 2005 introduces a new concept called broker service. This broker service allows asp.net to serve as a client where it can be notified if there is a change. For that we need to setup a dialog between asp.net 2.0 and sql server 2005. To enable that simple change, change the sqldependency attribute in the OutputCache directive.

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

5. Now open Global.asax file and add this line inside the function Application_Start(..)

System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings[“NW”].ConnectionString);

6. Now go back to the web page and hit refresh. Now the webpage functions the same way but if you go back to sql profiler, you will see there is much less traffic going on back and forth. There is no polling going on at this point. If we do go change the data, and hit refresh, it shows the change right away. So we get the benefit of this page invalidation at the table level with a lot less traffic going on back and forth.

Conclusion:

We learnt output caching has been around for a while and two techniques to enable table level caching: The polling mechanism that is working with sql server 2000 and 2005, and the new feature of 2005 only which is the broker service called the command notification