SQLDependancy Caching

One of the new features in version 2.0 of ASP.NET is a new form of cCaching. This is a cool feature but not as easy to get working as it would seem.

In case you haven't heard about it, using SQL Dependency you can tell the Web Page not to refresh until the DB notifies it that the data has changed.

There are three steps you have to do to get this feature to work.

1. Add this section to your Web.Config file:

<connectionStrings>

<add name="pubs" connectionString="Integrated Security=SSPI;User ID=sa;Password=sa;Data Source=LONDON;Initial Catalog=pubs;Persist Security Info=False;Workstation ID=DLLOYD" />

connectionStrings>

<system.web>

<caching>

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

<databases>

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

databases>

sqlCacheDependency>

caching>

system.web>

Watch out because there are articles out there that say the tag should be CACHE not CACHING

2. You have to run an administrative utility (aspnet_regsqlcache) that enables a Database and Table for SQL cache dependency.

aspnet_regsqlcache -S (local) -U "sa" -P "sa" -d pubs -t authors -ed (Enable the DataBase)

aspnet_regsqlcache -S (local) -U "sa" -P "sa" -d pubs -t authors -et (Enable the Table)

aspnet_regsqlcache /? (Too see all the commandline switches)

3. On the page itself, in the Page Directive add the following line.

<%@ OutPutCache Duration=6000 SQLDependency=“pubs:authors“ VaryByParams=“*“%>