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:

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 : IHttpModule { static Timer timer; int interval = 5000; public String ModuleName { get { return "BackgroundService"; } } public void Init(HttpApplication application) { // Wire-up application events if (timer == null) timer = new Timer(new TimerCallback(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(context.Application["connstring"].ToString()); SQLiteCommand command = new SQLiteCommand("select tableName, ChangeID from changenotifications",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.Parse( reader["ChangeID"].ToString() )) context.Cache.Remove(tableKey); } } connection.Close(); } #endregion } }

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:

<httpModules>
<add name="BackgroundService" type="BlackbeltBLL.BackgroundService, BlackbeltBLL" />
</httpModules>

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 CreateTableDependency (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 = CreateTableDependency(table); // Create key dependency string[] s = new string[] {dbKey}; CacheDependency d = new 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(); SQLiteConnection connection = new SQLiteConnection(connstring); SQLiteCommand command = new SQLiteCommand("SELECT ChangeID FROM ChangeNotifications 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 page:

private void Page_Load(object sender, System.EventArgs e) { Label1.Text = DateTime.Now.ToString("r"); string connstring =Application["connstring"].ToString(); SQLiteConnection connection = new SQLiteConnection(connstring); SQLiteCommand command = new SQLiteCommand("SELECT * FROM BlogItems", connection); SQLiteDataAdapter 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 choice is yours: Response.Cache.SetExpires(DateTime.Now.AddMinutes(1)); Response.Cache.SetValidUntilExpires(true); // This tells the page output to be cached on all cache-capable network applications // 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.CreateTableDependency("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 BlogItems
BEGIN
UPDATE ChangeNotifications SET ChangeID =(select max(rowid) from blogitems) ;
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 = <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!

Download the Visual Studio.NET 2003 solution accompanying this article