Geeks With Blogs
Eron Wright - All Killer No Filler blog
Did you know that you can achieve table-level cache invalidation with ASP.NET today?  Here's how:

The stock ASP.NET 1.0 CacheDependency class can monitor a local or network file.  As the file changes, the appropriate cache entries are evicted from the cache.  We can leverage this to achieve near-realtime cache invalidation as database data changes. 

  • Write a trigger for each table you want to monitor.  The trigger will touch a file on the database's filesystem.  The filename will correspond to the table name.  This can be achieved by invoking the FileSystemObject scripting object.
CREATE PROCEDURE TouchFile(@FileName varchar(255)) AS
@FS int, @OLEResult int, @FileID int
@OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 RAISERROR ('could not create FileSystemObject',16,1)
-- touch the file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 2, 1
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
  • Share the relevant folder in the database's filesystem.
  • Setup CacheDependency instances to watch the appropriate files for changes.   Use methods on the Response object to interoperate with page-level output caching.  Use the raw caching API for arbitrary data caching.

This technique offers a number of advantages over the proposed polling mechanism in Whidbey (see earlier post).

  • The invalidation will occur more quickly than with a polling mechanism.
  • No polling is used - the caching API uses the FileSystemMonitor component, that in turn leverages a callback mechanism to detect filesystem changes.

Other notables about this approach:

  • Like the Whidbey mechanisms, it is web garden and web cluster friendly.
  • An extremely narrow race condition might exist.  It occurs when two or more triggers attempt to touch the same file.  One will succeed, others will fail.  Will this result in stale data?  Given the asynchronous nature of FileSystemMonitor, it is extremely unlikely.  Nonetheless, you might want to bat around the scenarios in your head.  
  • If the file cannot be touched, the procedure will nonetheless complete.  That is, the race does not defeat the transaction and is safe.
  • This is a hack.
Posted on Wednesday, October 29, 2003 2:05 PM Technology | Back to top

Comments on this post: Database-driven cache invalidation in ASP.NET 1.0

# re: Database-driven cache invalidation in ASP.NET 1.0
Requesting Gravatar...
Can we use?

xp_cmdshell "dir C:\ >> urfilename.ext"

1) I think we create a tigger on table for which we wnat to watch changes and use the above command in that tigger.

2) Setup CacheDependency instances to watch the appropriate files for changes.

3) follow all the above step.

I had implemented the above and it work fine.

Please let me know ur comments.


Left by Surjit S. Walia on Oct 29, 2003 8:29 PM

# re: Database-driven cache invalidation in ASP.NET 1.0
Requesting Gravatar...
To clarify your idea, you have the trigger execute "dir c:\ >> somefile" in the command-shell. I wonder if the command will block, rather than throw, if the file is in use at the moment. If that is true then this is a nice hack. I do not know the performance implications of launching a command-shell. Certainly the COM approach is going to be faster.
Left by Eron Wright on Oct 31, 2003 10:56 AM

# re: Database-driven cache invalidation in ASP.NET 1.0
Requesting Gravatar...
UR code is good. This was just an idea.

Not necessary to Execute dir command any thing that creates file(eg. copy '1' >> a.txt). Even if the file is opened by other then also it work.

Yes when it made read only it doesn't.

Error we can trap as Below code.
Declare @Ret int
Exec @Ret = master..xp_cmdshell "copy >> c:\yourdir\a.txt" , NO_OUTPUT
if (@result = 0)
print 'Success'
print 'Failure'

Left by Surjit S. Walia on Nov 04, 2003 1:32 AM

Your comment:
 (will show your gravatar)

Copyright © Eron Wright | Powered by: