Continuously updating data in application from database

Question

Hello, I am writing an application that needs to continuously update its displayed data from a view in a database. Currently, it is periodically polling the entire data set and redisplaying it. I am changing it to be more efficient and am interested in people's opinions on how to do this most effectively.

The application is a Windows Forms application and uses a DataGridView to display the data. The database server is SQL Server 2008.

I've been reading about SQL Server change tracking features and am wondering if that might be relevant? Can anyone suggest any good approaches to take here? I would need it to insert, update and delete rows in the application without affecting the state of the current selected row or position of the scroll bars.

Thanks!

Answer 1

The simplest way is to query by timestamp column. When you first run your app it will query entire dataset and remember the greatest timestamp. Then it will poll with query where timestamp > @currentTimestamp and remember it again.

Answer 2

If I were tackling this problem, I'd look into doing it as such:

1. Create a SQL Server database project
2. Add a trigger to the project
3. Have that class send notifications, possibly using MSMQ, to the interested app or
4. Look into registering an event handler with that SQL Server class... not sure how or if that would work, but raising an event to your app from the trigger would be ideal

I can get more specific if you need, but that's the general approach

Info on triggers in SQL Server projects: http://msdn.microsoft.com/en-us/library/938d9dz2.aspx