SQL Cache Dependency in ADO.NET

SQL cache dependencies are more powerful when paired with SQL Server 2005
- you can set the cache dependencies are more granular

- this is not rely on polling but on query notifications from SQL Server 2005
- this require no preparation of the database

Here are the steps on how to enable the SQL cache dependency on a table.

1. Enable notifications using SQL registration tool
To enable SQL cache dependency, first you have to register the database, tables by using the Sql registration tool (C:\Windows\Microsoft.NET\Framework\v2.0.50727)
At the command prompt, type
aspnet_regsql -S [servername] -E -d [databasename] -ed -et -t [tablename]
-S = Server
-E = TrustedConnection
-d = Database
-ed = Enable caching at database

-et = Enable caching at table
-t = table

other options, -u = username, -p = password

After you run the statement, you hit refresh at your database.
You will notice there is an extra table created to store the cache data and a trigger created for the particular table. When there is changes on the table, the trigger will be fired up and write the changes to the cache table.

2. Register the notification in the Web Configuration
Enable the sql cache dependency at the web configuration file and set the poll time.
<system.web>
       <caching>
             <sqlCacheDependency enabled="true" pollTime="2000">
                    <databases>
                         <add name="conn" connectionStringName="conn"/>
                  databases>
             sqlCacheDependency>
     caching>
system.web>

The poll time is specifies how often the application checks to see whether the data has changed.

3. Define SQL Dependency
There are two options to define the SQL dependency
- You can specify on the OutputCache directive
<%@ OutputCache Duration="10" VaryByParam="*"
SqlDependency="[sqldependencyname]:[tablename]" %>

- or, specify directly on a datasource control
<asp:SqlDataSource EnableCaching="true" CacheDuration="Infinite"
SqlCacheDependency="[sqldependencyname]:[tablename]" ... />

After you have defined the SQL dependency, any changes that made to the database will be reflected on your web pages immediately though you have set the cache duration on the page.
This can make sure that you users always get the latest data from the server.

4. Notification-based cache invalidation
If you are using SQL Server 2005, you can use the Notification-based cache invalidation.
Unlike polling based validation, no needs to be registered in your application's configuration. Furthermore, no special configuration using the aspnet_regsql.exe tool is needed.

A notification based dependency is configured on the OutputCache directive using the string CommandNotification.
<%@ OutputCache Duration="10" VaryByParam="*" SqlDependency="CommandNotification" %>
This value indicates to ASP.NET that a notification based dependency should be created for the page or datasource control.

System.Data.SqlClient.SqlDependency.Start() method must be called before the first SQL query is executed. This method could be placed in Application_Start() event in global.asax file.