When using WPF in a smart client environment it always seemed natural that with a local datastore I should be able to listen to data changed events from the database. I have bundled a couple of systems together in a spike* to show that it is possible. Note the style implemented here is only useful where the database has one user (ie a smartclient application).

*spike is my way of excusing awful code.

VisualClue

I have taken the databinding goodness from WPF that automatically updates thanks to INotifyPropertyChanged. I have thrown a little visual clue in to show the data that has been modified. The row that was updated glows yellow.

Next I use a dictionary of WeakReferences in my repository to only hand out pointers to “Customers” in the dictionary. As they are weak references its not a big overhead to store them.

Next I use the service broker technology available from SQL 2005 up. The great thing here is that it is available on SQL Express which is perfect for Smart Client applications. Basically I add a trigger to each table (only customer at the moment) and add all the modifications to a queue as XML data.

Last I have a class that listens to the database called  RepositoryNotification. It basically runs in the background thread and using the Service broker queue technology it waits for triggers to place XML on the queue and then returns it as data. This all works great. At the moment the code that translates the xml is just awful so my apologies up front.

WpfWithBoundRepositoryCache.zip

Note this quick spike currently only supports one row updates at a time (due to my crappy XML de-serialization) and doesn’t support inserts or deletes. Inserts and deletes could possibly require some “business logic” to identify if the new row should be shown or the deleted row to be removed.

–Edit

Adam Machanic has a brilliant tutorial on Service broker for those interested. Most of my SQL code was ripped from these tutorials. I think it was supposed to be a 3 part series but I can only find part 1 and part 2.