Saturday, July 7, 2007

ASP.Net: Using cache to determine a time for content reloading (rather than reading data from cache)

I have been experimenting with this recently, ASP.Net has a good, simple, quick caching model. Cache.Insert("PageReloadTimer", "Yes", Nothing, Datetime.Now.Addminutes(5), Timespan.Zero) will give you a cache of the word "Yes", named PageReloadtimer, with no dependencies, for 5 minutes until it is deleted. Caching is of course great for optimizing performance, as you can cache data instead of getting it from the source, which is great for screen scrapes and other data feeds. However, it could also be used as a simple timer, I'm sure there are other ways to do this, but this seems simple enough (comments are appreciated though). The scenario I had, is on my companies local intranet, we want to display data from an excel file, as you will see in my next post, excel files can be used in ASP.net as a datasource, just as if it were an SQL database. However the performance is obviously reduced (if you don't use a where statement pages take a very long time to load - so a where statement is essential, even if it just eliminates nulls). I decided we would like this to be stored in our SQL database, for easy querying, and so it will be backed up, as it was financial data. So I made the page query the excel sheet, then insert all of the data into our SQL database, I decided this only needs to be done every say, 12 hours, so, to tell it to do this I simply did a Cache.Insert("PageReloadTimer", "Yes", Nothering, Datetime.Now.Addhours(12), Timespan.zero) then added an if statement to the start of the excel query, If Not Cache("PageReloadTimer") is nothing then..... This skips the excel loading, and goes straight to reading the data from the SQL database. Works perfectly. I could even change the "Yes" which doesnt really mean anything, to a timestamp of when it was updated, allowing me to add "this information was last updated on ..." to the page. I could of course, have cached all the actual data, and used regular expressions to query it, but that wouldn't have given me the advantages of an SQL database, and would have been extra unnecessary coding. You can read more on caching at 4 Guys from Rolla, and OnDotnet.

No comments: