Using SqlCacheDependency to get real time updates? - ASP.NET

Question

I would like to display real time updates on a web page (based on a status field in a database table that is altered by an external process). Based on my research, there are several ways of doing this.

• Long Polling (Comet) - This seems to be complex to implement
• Regular Polling - I can have an AJAX method trigger a database hit every 5seconds to get the current status. But I fear this will have performance issues.

Then I read about using SqlCacheDependency - basically the cache gets invalidated based on a field in the table. I am assuming I can use the event trigerred when the cache is invalidated to show the new update to the user?

What's an easy solution that will not have performance issues?

anyone?

Answer 1

Instead of polling the database a more scalable and performant approach would be to poll for the existence of a file on your web server, something like a lightweight js file. The contents of the file is not important, but to give you an idea you could have a JSON object of some sort which describes in greater detail the result of the process.

Then your background process which is doing the processing as the final step can create the file or call a web service on your web tier to do so.

Here's how it could work.

Users presses button which posts to server

Server kicks of process and returns an identifier e.g. C3201620-E622-4fe2-9F3A-E02FFA613F59

Web UI then polls peridodically for the existence of C3201620-E622-4fe2-9F3A-E02FFA613F59.js, the javascript would manage the 404 error and keep retrying until it receives a 200

Hope this gives you some ideas.

Code example to handle 404's in jQuery

$.ajax({
        url: '/CheckForStatusChange/C3201620-E622-4fe2-9F3A-E02FFA613F59.json',
        type: "GET",
        success: function(result) {

        },
        error: function(request, status, error) {
        //handle error here and setTimeOut                 

        }); 

Answer 2

You have 2 issues:

1. Retrieving the data to push
2. Pushing the data to the clients

Item #1 is easy, assuming you have a fairly efficient query, and you're not querying it for every user; just query, get the data, and push it. You could use the SqlDependency option, or you could just poll it; it depends on how heavy your data is.

Item #2 is a little trickier, because you'll need to either hit the server constantly, or use a comet server. We've written a highly scalable .NET comet server, WebSync, over at Frozen Mountain that might fit the bill. Essentially, you have a separate process that drives the publishing, and WebSync would handle the pushing of the data to your clients.