Cache dependency on database select statement

[martin]

Hi,

I have a page that contain a dropdown list of values.
This drop down list rarely changes so I wish to cache the page. However the
values in the dropdown box are taken from a database, so if the values in
the database change I wish to expire the cache and go to the database again
and get a fresh copy.

I have worked out that I should use one of the methods of cache.insert that
take a cache dependency object, for example

public void Insert(
string key,
object value,
CacheDependency dependencies,
DateTime absoluteExpiration,
TimeSpan slidingExpiration
);

However I am not sure how to create the cachedependency object from an
sqlstatement, and conect it to my database. my db is sql server 2000, the
select satement is of the form "select catID,CatName from category" and only
references a single table.

I have been able to create a cachedependency object that relies on an XML
file

dim objCache as new cachedependency(server.mapath(/myXMLfile))

however I am stumped creating a dependency on a database, is this possible,
Iwould be gratefull for any advice on how to do this.

cheers

martin.

 

[Steve C. Orr [MVP, MCSD]]

It is possible, but not easy or graceful.
Here's the details:
http://msdn.microsoft.com/msdnmag/issues/03/04/WickedCode/default.aspx