Sql Caching in .Net 2.0

Caching has always been a powerfull feature in Asp.Net, However in Asp.Net 2.0 It's even more powerfull.Asp.Net 2.0 has different kinds of Caching, One of most interest is SqlCaching which enables you cach your pages depending on changes in database Not only on page level but also on SqlDataSource Level.Am gonna take you in a simple steps on how to configure an use this powerfull feature and put you on the road.

In the examples I used the popular Northwind database and two of Its tables "Categories and Products".

To use SqlCaching you need to play in Three places:

1- Your DataBase.
2- Your web.config.
3- your Page ".aspx" (Note that you can use sqlcaching from your .net code and thus you don't need to configure your web.config nor your .aspx but this works only with sqlservers 2005 cause It uses Notification services.).

Configuring DataBase

To configure you database you will use the aspnet_regsql.exe utility that comes with .net sdk and enables you to install many asp.net 2.0 features like membership, sessionstate and Caching (use aspnet_regsql.exe /? to list all options of that tool)

1-from your start menu open Programms/VisualStudio 2005/VisualStudio Tools/VisualStudio Command Prompt.

2-To enable your database"Northwind " for caching use this aspnet_regsql.exe -E -d Northwind -ed
where -E means use windows authentication when connecting to the database server, -d is the database switch and -ed means Enable database Caching

Then write this
aspnet_regsql -E -d Northwind -t Categories -et
here -et means Enable table Caching.

Now if you go to your Northwind database you will find a new table called AspNet_SqlCacheTablesForChangeNotification along with multiple stored Procudures and a Trigger on the Categories table that will be triggered each time a change happen to that table which in turn will invalidate the cache.

Configure wen.config

1- Add a connectionString to the ConnectionStrings section like that





2- Add Caching section to your web.config System.web section like that



In the caching section we added a sqlCacheDependency enabled = true and poll time to one second since it's in milleseconds."Poll time determines how often will asp.net go to sql to check if the data has been changed. Inside the sqlCacheDependency is a databases section that you use to define the database for this sql caching "yopu can provide as many as you want since the name is different".

Configure you page

Add the outputCache directive in your page as follows



Note The SqlDependency attribute value consists of name of SqlDependency from web.config, colon and then the table name on which caching is work. and that's It in Its simplest implementation. Note that I didn't intended to cover everything here, It's only a starting point for you on how to use It and there are many other features and tricks on how to use SqlCaching in ASP.Net 2.0.

The main idea is that:

when you run the aspnet_regsql tool It creates a table with 3 columns : tablename,notificationcreated and changeID and some stored procedures and a trigger on the table you provided gor caching. Whenever a change occur in the table the trigger is fired and update the changeID field in the corresponding record for that table in the generated table. when you request a page that sqlCache enabled for, It check the poll time in web.config and if It expired it goto database and check the changeID field and if its value was changes it invalidates the cache. Understanding that infrastructure gives you flexibility on using this fetaure like making cache based on subsets of data rather than the whole table by making your own trigger and inserting the record yorself in the generated table.

Enjoy...