Yesterday, I spent some time thinking about caching.
Specifically, caching data.
I am using ASP.NET 4.0, coding in VB. I'm using a MySQL database, so cannot use the SQL Server-only sqlDependency.
It is important to me that all data changes are acted upon and no user sees old data, so I have had to come up with something to monitor database changes on specific tables.
What I have come up with is this...
1: A routine which creates a Key/Value Dictionary in the Cache which contains any table names which have recently been written to, and a write-time timestamp, ie:
tableName | timeStamp (Unix)
table2Name | timeStamp
2: A commonly available function which is called when writing any data, ie all INSERT, UPDATE or DELETE commands are created by passing two arguments to this function, sql and tableChanges.
In this function, sql is simply the UPDATE/INSERT code and tableChanges is a comma separated list of table names which will be affected by this command.
It is during any amendment that the table in point 1 is created.
3: A commonly available getData function, which takes arguments:
cacheName As String - the cache key name
slidingTime As Double - sliding expiry period (converted to minutes within the function)
dependancy As Object - standard cache dependancy object
newSql As String - the query required to get this data
Optional tableDependencies As String = "" - a comma separated list of tables which this data is dependant on
4: A function for writing a data object to the cache based on similar arguments to those above.
Now, function #3 first checks if the cache contains the key passed into it, if not, then it simply goes off to the database and fetches new data.
If it does contain this key, then it does two things, firstly it gets a time stamp of when this item was added, which is always cacheKey_added (created in the process of adding a cache item), then having got this time stamp, it grabs the comma separated tableDependencies, turns it into a List of table names, loops through them all checking the corresponding table update time (using table name as key) from the cached data Dictionary created in the first function.
If any of the table update times are more recent than the cache time, then it goes off to the database and fetches new data, if not, it returns the cached data.
Similarly, if there is no Dictionary of tables held in the cache it returns cached data as the assumption is no changes have been made as this cache object is only created when a change is made to the database.
The data is always held on a sliding cache time, typically quite short, and of course data which doesn't need caching at all can be grabbed directly without this process.
I haven't posted the code for any of the above as it's not necessarily relevant to my actual question, but the background is important.
So, my actual question is:
Given that the above works fine for determining when any tables in my database have changed (providing nothing is changed in phpMyAdmin, but as only one person has access to that, in my case this is fine... how can I apply the same principle to caching User Controls or Pages?
So, I have cached user controls using VaryByParam="PARA" and VaryByCustom="CUSTOM_VALUE" successfully in the past. It stands to reason that VaryByCustom is the way to go... but how would I reliably pass parameters (table names and last cache time of the page) into a custom function to do this... OR, am I talking nonsense?!
Additionally, is there any way to get the cache write time without having to save an additional _added key to every cache item?
