| One of the
coolest features of the Cache class is the ability to exert fine-grained
control over its behavior with various types of dependencies. The file-based
dependency is one of the most useful - a file dependency is added
by using Cache.Insert and supplying a CacheDependency object referencing
the file we want to have monitored:
Cache.Insert("MyData", Source,
new CacheDependency(Server.MapPath("authors.xml")));
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
|