Page level caching with SqlDependency

This article will show you how to create page level caching with sqldependency. Caching is something that can prevent your database roundtrip, reduce the server load and faster your website. But think before using caching in your website take decision where to apply caching and where to not.

You can use sliding expiration of cached pages but what if you want only conditional cache expiration?

Well here I'm not going discuss all types of caching here I'll show you how to apply the caching with sqldependency(Conditional cache expiration).

Here dependency means as long as the dependency remain unchanged the cache will remain intact when the dependency table have some changes[Database anamolies like Insert, Update, Delete] etc. then your cache will expire and will regenerated with new changes in data.

To apply this caching you have to follow the following steps:
Enabling the database for SQL cache dependency

C:\> aspnet_regsql -S <servername> -U <username> -d <databasename> -ed -et -t <tablename>
Password: <Enter your password>



It'll create a table [AspNet_SqlcacheTablesForChange] and trigger for that table in your database



And a trigger in Trigger section related to the table ProductCategories

Now add sqlCacheDependency section in your web.config file

   <system.web>
      <caching>
        <sqlCacheDependency enabled="true" pollTime="2000">
          <databases>
            <add  connectionStringName="__defaultCon" name="__defaultCon"/>
          </databases>
        </sqlCacheDependency>
      </caching>

It'll make a round trip to database at every polltime and will check for any change.

Now there is two ways to add the Cache of page to the data of table

1. Adding a page level caching tag with sqldependency on a table

<%@ OutPutCache Duration="20" VarByParam="*" SqlDependency="__defatulCon:ProductCategories" %>

This will monitor the table ProductCategories for any changes [insert, update, delete etc.] and if found any then regenerated the cache with new data.

You can user sqlProfiler to check the roundtrips log and poll event.

To reduce this check for every time the poll occur. use CommandNotifation in SqlDependency attribute of OUtputCache page tag.

2. Adding a page level caching with sqldependency on CommandNotification.

<%@ OutPutCache Duration=20 VarByParam="*" SqlDependency="CommandNotification" %>

But you are not done yet when using this type of setting. Now add the Global.asax file and start the sqlDependency check service on application start event. Add following code line in Aplication_Start event.

protected void Application_Start(object sender, EventArgs e)
{
    System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["__defaultCon"].ConnectionString);
}

You can use any of these setting and increase the performance of your site.

Cheers