Polling based invalidation technique involves checking at
frequent intervals if the data was updated. This method may cause stale data to
be served from the cache if the underlying data changed between the polling intervals.
The longer the polling interval, the longer the time it takes to pickup the
changes and invalidates the cache. With this method, you will also need to use
aspnet_regsql.exe command to enable notifications for the database/tables.
Following are the steps to use
SqlDependency with this technique
1) Enable notifications for the database using the
aspnet_regsql.exe tool. The syntax is provided below.
aspnet_regsql.exe -S <Server> -U <Username> -P
<Password> -ed -d Northwind -et -t Employees
If you want to look up what these switches mean, here is the
link to MSDN.
Example: aspnet_regsql.exe -S
"MachineName\InstanceName" -E -d "MyDatabase" -ed
NOTE: This only needs to be done once for each database.
2) Enable notifications for the table(s).
aspnet_regsql.exe -S "MachineName\InstanceName" -E
-d "MyDatabase" -et -t "MyTable"
3) Configure polling in web.config file of the application.
Listing 5
<system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="1000" >
<databases>
<add name="FriendlyDatabaseName"
connectionStringName="ConnectionStringNameToUse" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>
The poll time specifies how often (in milliseconds) the
application checks to see whether the data has changed.
4) Configure SQL dependency using the OutputCache directive
below or for the DataSource control:
On an ASPX page:
Listing 6
<%@ OutputCache Duration="999999" SqlDependency="DatabaseName:TableName"
VaryByParam="none" %>
On a datasource control:
<asp:SqlDataSource EnableCaching="true" CacheDuration="Infinite"
SqlCacheDependency="DatabaseName:TableName" />
