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 are not 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 does not 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 Toget!
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:
Using system;
Using system.Web;
Using system.threading;
Using system.data; using system.data.sqlclient;
Using system.configuration;
USING FINISAR.SQLITE;
Namespace BlackBeltbll {
Public class backgroundService: httpmodule {
Static Timer Timer;
INTITTERVAL = 5000;
Public string modulename {
Get {return "backgroundservice";
}
Public void init (httpapplication application) {
// Wire-Up Application Events
IF (Timer == Null)
Timer = New Timer (New TimalCallback (ScheduledWorkcallback), Application.Context, Interval, Interval;
}
Public void dispose () {
Timer = NULL;
}
Private Void ScheduledWorkCallback (Object Sender) {
HttpContext context = (httpContext) Sender;
Poll (context);
}
Void Dosomething (httpcontext context) {
}
#Region DB Poll
Void Poll (httpcontext context) {
SQLITECONNECTION CONNECTION = New SQLITECONNECTION (CONNSTRING "]. TOSTRING ());
SQLiteCommand Command = New SqliteCommand ("Select Tablename, Changeid from Changenotific); CONNECTION
Command.commandtype = commandtype.text;
IDataReader Reader;
String Key = configurationSettings.appsettings ["sqldependency"];
Connection.open ();
Reader = Command.executeReader ();
While (Reader.Read ()) {
String TableKey = String.Format (Key, Reader ["TableName"]);
IF (Context.cache [TableKey]! = null) {
INT ChangeKey = int.parse (Context.cache [string.format (key, reader ["tablename"])]. TOSTRING ());
IF (ChangeKey! = INT.PARS (Reader ["ChangeID"]. TOSTRING ()))
Context.cache.remove (TableKey);
}
}
Connection.Close ();
}
#ndregion
}
}
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 wherever your specific business logic calls for). Your httpmodule is registered in Web.config as required, Like this:
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):
Using system;
Using system.Web;
Using system.Web.caching;
Using system.data;
USING FINISAR.SQLITE;
Using system.configuration;
Namespace BlackBeltbll {
Public class sqlcachedependency {
Public Static String CreateTablesDependency (String Table) {
String dbkey = configurationSettings.appsettings ["sqldependency"];
// Format Our Cache Key
DBKEY = String.Format (DBKEY, TABLE);
IF (httpContext.current.cache [dbkey]! = null)
Return DBKEY;
// Add Table Entry Into the cache
IF (httpContext.current.cache [dbkey] == null) {
HttpContext.current.cache.insert (DBKEY, GETTABLEKEY (TABLE);
}
Return DBKEY;
}
Public Static String CreateTabledePendency (String Table, String Key, Object Value) {
String dbkey = cretetabledependendency (TABLE);
// CREATE Key Dependency
String [] s = new string [] {dbkey};
Cached Cachedependency (NULL, S);
// do the actual insert
HttpContext.current.cache.insert (Key, Value, D); Return DBKEY;
}
Public static int getTableKey (String Table) {
String connString = httpContext.current.Application ["connString"]. TOSTRING ();
SQLITECONNECONNECTION = New SQLITECONNECTION (ConnString);
SQLiteCommand Command = New SQLiteCommand ("Select Changeid from Changenotification Where Tablename = '" Table "'", Connection;
IDataReader Reader;
int changeID = -1;
Connection.open ();
Reader = Command.executeReader (Commandbehavior.CloseConnection);
Reader.Read ();
ChangeId = (int) Reader ["ChangeID"];
Connection.Close ();
Return ChangeId;
}
}
}
Now you are ready to handle the caching action right in your
Private Void Page_Load (Object Sender, System.EventArgs E)
{
Label1.text = datetime.now.tostring ("r");
String connString = Application ["connString"]. TOSTRING ();
SQLITECONNECONNECTION = New SQLITECONNECTION (ConnString);
SQLiteCommand Command = New SqliteCommand ("Select * from blogItems", connection);
SQLITEDATADAPTER Da = New SQLITEDATAADAPTER (Command);
Connection.open ();
DataSet DS = New Dataset ();
Da.fill (DS);
DataGrid1.datasource = DS.TABLES [0];
DataGrid1.databind ();
Connection.Close ();
// We are going to cache this guy for only one minute. The choium is yours:
Response.cache.sexpires (DateTime.now.Addminutes);
Response.cache.setvaliduntilexpires (TRUE);
// this Tells the page Output to be cached on all cache-capable network coplers
// That Are Involved in The Response. This Includes The Requesting Client, The
// Responding Server, or a proxy server through which the response passes: response.cache.setcacheability (httpcacheability.public);
String cachekey = Sqlcachedependency.createtablededependency ("blogItems");
// this AddS Our Sqlcachedependency to the response cache:
Response.addcacheitemdependency (cachekey);
}
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 BlogItemsBegin Update Changenotifications Set ChangeId = (Select Max (Rowid); END;
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 =
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 business is a lot cheaper touring new hardware at your scalabilty problem!
Download The Visual Studio.Net 2003 Solution Accompanying this Article
