Best way to keep GUI updated when the underlying SQL view data changes?

Question

Let's say you have a form with a component that displays data from a view on an SQL server. The component would be something like a ListView or DataGrid - basically something that can display the data in a 2D grid format. Another application changes the data that the SQL view describes, and does this regularly but at undefined intervals.

The problem is this: the form needs to keep itself updated with the most current data, with the minimum of latency between it changing and it displaying.

The platform constraints are that you must use .NET 2.0 and Microsoft's SQL Server. Assuming that you have full control of all other parts of this system and can design it any way you want - what's the best way to do this?

The ones I've thought of so far:

• Regularly polling for the data and comparing to see what's changed. Either by:

  • reading the entire view from the SQL Server and comparing it against the currently displayed data, row by row, updating the displayed data as it goes, or
  • somehow recording a table of changes to the view (using triggers or application logic), reading new items in there each time, and applying this to the displayed data.

• Adding a layer in between the SQL Server and both the form and the updating application, so any updates from the main application go here and the form application can be notified of changes in real time. Not really sure how this would be done in practice though.

• Putting triggers on the tables that somehow cause the form to be notified - calling some custom extended stored procedures maybe?

Anyone who has done this before, please offer your insights!

Also please comment if you know of any existing libraries that do all this already.

Answer 1

Read up on Query Notification. If there are not too many queries or clients that need to be monitored it can be a good solution.

Answer 2

In our little internal app, whenever a user makes a change to the data in the database, a UDP broadcast is sent over the network telling everyone what sort of data has been modified. If a user has part of the gui open that matches what has been broadcast, the form they have open is reloaded.

Since it is not overly common that users of our system have open the same bits of data, this is not an overly common occurance. We do though send out alerts when someone is viewing the same data that they have open - to let them know what might be causing the data that they are looking at to change.

Answer 3

Generally speaking, #2 is the way you want to go if you want your app to scale at all. Query notifications generally won't work, since you have to keep a connection open to the database.

Triggers are a bad idea since the database is not always going to be the exact representation of the model that you have. It might but it's not a requirement.

This is why it's a good idea to funnel your updates through another layer which can then send notifications back when the model changes (and in a way that doesn't require a persistent connection and scales).

You can also cut down on traffic by having the clients subscribe for what notifications that they want, so the server doesn't end up sending out too many notifications to clients that don't care.

Answer 4

SQL Server does provide for notifying clients of updates to a specific table. Depending on the volume of updates that happen this may be the approach to take. I think it requires SQL 2005 or later though, but I can't recall for sure.

If you have a high volume of updates to the source table, your second suggestion could work. One approach would be to utilize WCF, and utilize a call back mechanism. The service would have to be either a singleton or sessionful, but it would keep track of what clients are connected to it by having each client register a callback with it when it opens the proxy. Then you'd have the clients submit the updates through the service and as the last action in the update transaction to the database you'd have the service loop through all the clients and notify them up the update (I'm assuming with the updated data so you don't requery the database). This of course would mean additional load in the middle tier, but it could definitely work.