ASP.NET SQLCacheDependency with SQLite and Triggers
The SQLite database engine is getting better all the time, with Version 3.0.6 now out and 3 "Final" not too far off. If you aren't familiar with SQLite, you might want to catch up by reading my previous article here. SQLite, as of this writing, is not yet fully ready for the Compact Framework, but it should be, very soon. Now that should be interesting to any developer who thinks "mobile"!
Another cool feature of SQLite, besides its speed, very small size (221KB for the entire engine), zero - impact installation, the Finisar ADO.NET provider and more -- is the fact that this little db engine supports triggers. Coupled with some background thread polling, its not only possible -- but quite easy -- to wire up SQLite with a ChangeNotifications table and an insert trigger a - la Rob Howard's Tech-Ed 2004 demo, and have yourself a full-fledged SQL CacheDependency.
Not to be daunted, I took Rob's excellent sample "BlackBeltBLL" code, modified it to use SQLite and the ADO.NET SQLite provider, added text SQL calls where stored procedures would be required (SQLite doesn't support stored procs, but then hey - at 221K what do you expect?) and before you can say Connection.Open, I had a full working demo version put together!
For a quick refresher and an excellent article that was pointed out to me on one of my previous article's "bottom discussion" forum posts by a savvy reader, here is Bart DeSmet's synopsis of how SQLCacheDependency works on ASP.NET 2.0 and how you can adapt it to work in ASP.NET 1.1.
For a refresher on Rob Howard's code for ASP.NET 1.1, follow with me:
First, we need an HTTPModule:
Basically this just kicks off a background thread and polls your ChangeNotifications Table every X milliseconds, and if your changeId has been changed by your trigger because of an inserted record, it invalidates the Response Cache, forcing your page's code to actually "re-hit" the database to populate your DataSet (or whatever your specific business logic calls for).
Your HTTPModule is registered in web.config as required, like this:
<add name="BackgroundService" type="BlackbeltBLL.BackgroundService, BlackbeltBLL" />
You also need a custom SQLCacheDependency class (In ASP.NET 2.0, you have a built-in one that is now "unsealed" and from which you can easily derive your own custom class):
Now you are ready to handle the caching action right in your page:
Our trigger code is very simple: We have a main "BlogItems" table and a utility "ChangeNotifications" table, containing two columns, "TableName" (which table we are monitoring) and "ChangeID" - an integer used for comparison to the cached id.
In SQLite, trigger syntax is a bit different than Transact SQL:
CREATE TRIGGER insert_BlogItem INSERT ON BlogItems
UPDATE ChangeNotifications SET ChangeID =(select max(rowid) from blogitems) ;
Whenever we have a row inserted into BlogItems, the trigger fires and sets the ChangeID to the max value of rowid, an internal counter value used by SQLite. Normally we would have more than one row in the ChangeNotifications table and the update would require a WHERE TABLENAME = <TABLENAME> clause, but in this case it will only ever be BlogItems so I've left it out. SQLite has a command line SQLITE.EXE "OSQL" clone that you can download, or you can use the SQLiteQA freeware product and modify the connection strings to accept SQLite 3.0 databases.
And, you can try it out live, if you like, before you download the full solution, Right here. if you repeatedly refresh the page, you will see that the Date-time label does not change. However, if you add a new record, the cache is invalidated and when you are returned to the main page, you'll see your new record - which could only have been retrieved if a brand new database call had been made! (If there are too many records from various people fooling around, just hit the CLEANUP button to delete some).
The economies of scale obtainable through a SQLCacheDependency such as this can be huge - on the order of going from some 100 requests per second without caching, up to as much as 800 requests per second using caching, or even higher. Learning caching techniques as a developer is a lot cheaper than throwing new hardware at your scalabilty problems!