Good C#.NET Solution to manage frequent database polling

Question

I am currently working on a c# .NET desktop application that will be communicating to a database over the internet via WCF and WCF Data Services. There will be many spots in the application that may need to be refreshed upon some interval. The easiest solution would be to just put these areas on a timer and requery the database. However, with thousands of clients connecting to the service layer and hence database, these operations would be very expensive to the server.

What I have considered is creating an RSS feed that is polled by the client, and lets the client know when these particular areas need to be updated. The RSS feed will be managed by a service that either polls the database for changes, or iterates through a list of items that are queued up by the WCF requests made by the client.

I have also considered creating some direct and continuous connection from the client to the server, but I am not sure what outbound firewall ports would be open from the client. I could probably only count on port 80/443.

So my question is what solutions have people had success implementing to solve this problem? Have people done RSS? Microsoft Sync Services? Two way communication between client and server over some save port via WCF?

Answer 1

I think you might want to go with a combination of two approaches. First off, you could use long polling from the client to server so that the server can notify the client as soon as a change occurs that the client is interested in.

A new technology that handles the above suggestion quite nicely in ASP.NET is SignalR. This handles much of the details of long polling (or uses WebSockets when it can) so you don't have to worry about it.

Secondly, based on the tags in this question it looks like you're using SQL Server. You could use database notifications on the tables you are interested in to have the DB notify your service when changes occur. This could then trigger the service to notify the client about the changes through the long poll connections. You can do this using the SqlDependency class.

I'm sure there are other ways, but this would probably scale quite well as you'd only have one service getting the notifications and then distributing them out to all the clients.

Answer 2

you can define a callback interface in the WCF connection like this:

[ServiceContract(CallbackContract = typeof(IFooClient))]

As the client initiates the connection the should work through the firewall. The the server can method to register for changes an you can get the callback interface with

IFooClient client = OperationContext.Current.GetCallbackChannel<IFooClient>();

and callback all the clients that are registered on data changes.