Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Clear .Net Caches upon Database Trigger

.Net offers some very appealing cache capabilities, including timed cache clearing.  However,  it's often not elapsed time that matters; it's the presence of new information (new articles in a content management system, for instance, or new threads or articles in a discussion forum). 

Accepting for the sake of discussion the desirability of stored procecures,  it would be very useful to be able to flush cache on the completion of a database transaction.

Any idea how to accomplish this in .Net?

Steven F. Johnson
Thursday, September 19, 2002

As far as I know, there is no direct way to invalidate the ASP.Net cache directly from the database. I have seen discussions on using the COM capabilities built into SQL Server ( the same functionality may be possible from other platforms, ie. Oracle through Java ) using the sp_OACreate, etc... stored procedures.

Perhaps you could call a COM object from the database in this way that would call into the Cache directly or change a file that the cache depended on. Since I have not tried this myself, I do not know the feasibility of this. I would be delighted to hear some more opinions.

John Cartin
Thursday, September 19, 2002

Steven,

Rob Howard, a Program Manager on the ASP.NET team, has a write up on how to do this.

Here is the description:

"The ASP.NET Cache supports invalidation based on key, timestamp, or file dependencies, e.g. file changes and the cache item is invalidated. A frequent question is how to support this from a database, e.g. we're caching a DataSet, or some other reperesentation of data, in the ASP.NET Cache and the table(s) representing that data changes; thus the data in the Cache is no longer valid. This sample shows using some simple code that will automatically wire up a trigger on a table and call and extended stored procedure to call back into the ASP.NET application to invalidate the Cache. "

and here is the link

http://www.gotdotnet.com/team/rhoward/

The code sample is for Beta 2, so you may need to update it.

Anil

Anil John
Thursday, September 19, 2002

*  Recent Topics

*  Fog Creek Home