But what if we want to invalidate the Cache based on changes to our Database - a scenario that is highly likely in many application scenarios? There is no direct built - in Cache support for monitoring Database tables for changes. It turns out that with the use of a relatively infrequently used SQL Server system sproc, sp_makewebtask, it is possible to accomplish this objective. This sproc was designed to create web pages from queries, but with only the slightest modification -- employing it in a trigger -- we can gain a reasonably efficient way to "touch" a specified file on update, insert or delete from a specific table. This causes the file monitoring process in the CacheDependency instance to detect a change, and invalidate the cache. In fact, since CacheDependency works with UNC file protocol, we can have this work even throughout a web farm, where each machine's copy of the app monitors the same file on a single machine in the farm through a UNC path to the file.

Without further discussion, let's put together a sample Web Application to illustrate how this can be done in it's simplest form. First, we'll use the trusty stock Northwind sample database in SQL Server where we can show a simple DataGrid of the Employees table. (Poor Nancy - her record has been changed so many times <grin/>). The fitrst thing we need to do is set up our trigger:

CREATE TRIGGER WriteCacheDepFile ON [dbo].[Employees]
FOR INSERT, UPDATE, DELETE
AS
EXEC sp_makewebtask '\\peter\C$\Cache\mycache.txt', 'SELECT top 1 FirstName FROM employees'

What we did above is simply tell SQL Server that if anything at all happens to the Employees table, write a file "mycache.txt" based on a simple query. The query can really be anything at all; as long as it's valid T-SQL, SQL Server will happily update the file.

Next, we need to actually create the directory, and make it a share. You may also need to update permissions so the file can be written. Note that I've used the Administrative share "C$". You may also need to create an initial blank file, "mycache.txt".

Now we are all ready to create our app. First, let's put the dependency file into our web.config so it's easy to change without redeployment:

In web.config, near the bottom, add an appSettings section like so:

</system.web>

<appSettings>
<!-- modify line below to suit actual location of your cache folder -->
<add key="dependencyFile" value="\\peter\Cache\mycache.txt" />
</appSettings>

</configuration>

Now, let's set up our Cache mechanism in our Global class so we won't need any page specific code:

Public Class Global
Inherits System.Web.HttpApplication
Dim _cache As System.Web.Caching.Cache = Nothing
Public Shared blnRefreshed As Boolean = False

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
_cache = Context.Cache
RefreshCache(Nothing, Nothing, Nothing)
End Sub

Shared Sub RefreshCache(ByVal key As String, ByVal item As Object, ByVal reason As System.Web.Caching.CacheItemRemovedReason)

Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT EmployeeID, lastname, firstname FROM Employees", "server=localhost;database=Northwind;uid=sa;pwd=")

Dim ds As New DataSet()
adapter.Fill(ds, "Employees")
Dim onRemove As CacheItemRemovedCallback = New CacheItemRemovedCallback(AddressOf RefreshCache)
Dim depFile As String = System.Configuration.ConfigurationSettings.AppSettings("dependencyFile").ToString()

System.Web.HttpContext.Current.Cache.Insert("Employees", ds, New CacheDependency(depFile), _
Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, _
CacheItemPriority.High, onRemove)
blnRefreshed = True
End Sub

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
If HttpContext.Current.Cache("Employees") Is Nothing Then
RefreshCache(Nothing, Nothing, 0)

End If

As can be seen above, we' ve defined _cache as an object of type Cache. In our Application_Start method we set it to the current instance of the Cache, and call the RefreshCache method to fill it. RefreshCache is actually as Shared (static) delegate callback. What it does is simply retrieve a DataSet of the Employees table. It then sets up the required CacheItemRemovedCallback pointing to RefreshCache so that we can set up the dependency based on our dependency file. We set up our callback "onremove" to point to this method.

Finally, we insert the DataSet into the Cache along with our onRemove callback delegate. And in Session_Start, just to be "sure" I've added another optional checking call to RefreshCache to "bake it".

At this point, our app is all set up to use in any page that needs access to the cached DataSet. In WebForm1.aspx, I've illustrated how this can be used:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Make sure Cache item ain't empty, if it is, fill it up
If Cache("Employees") Is Nothing Then
Global.RefreshCache(Nothing, Nothing, 0)
cacheStatus.Text = "Cache Refreshed at " & DateTime.Now.ToLongTimeString
Else
cacheStatus.Text = " DataSet from Cache "
End If
Dim ds As DataSet
ds = CType(Cache("Employees"), DataSet)
DataGrid1.DataSource = ds.Tables(0)
DataGrid1.DataBind()
End Sub

And that's it! If you request this page, it will show that the DataSet is successfully retrieved from Cache every time. However, if you leave your browser open and fire up Query Analyzer pointed to your Norhtwind database, and execute some query such as 'Update Employees set Lastname = 'Davovlieu' where EmployeeID =1' which changes something in the table, and then re-request the page, then the next time it is loaded, you'll see that the cache has been invalidated and refreshed.

The Solution download below is Visual Studio.NET 2003. If you don't have 2003 yet, just start a new Web Application and bring in the web.config, global.asax and Webform1.aspx files and all their associated codebehind class files.

Download the code that accompanies this article