How can I update a Windows Form client every time a SQL Server table changes?

Question

I have a list form that shows information from a database. I want the list to be updated in real time (or almost real time) every time something changes in the database. These are three ways I can come up with for this:

• Set up a timer on the client to check every few seconds: I know how to do it now, but it will require creating and closing a new database connection hundreds of times per hour, regardless of whether there was any change
• Create something like a TCP / IP chat server, and every time the program updates the database, it also sends a message to the TCP / IP server, which, in turn, sends a message to the client form: I have no idea how do it right now.
• Create a web service that returns the date and time the last time the table was changed, and the client compared this time with the last client, updating: I could figure out how to create a web service, but I do not do this without creating communication with the database anyway

The second option seems not very reliable, and the first one seems to consume more resources than necessary. Is there a way to tell the client every time a database changes without connecting every few seconds in the database, or is it not so important to make so many database connections?

Answer 1

Try the SqlDependency class . It will fire an OnChange event whenever the results of its SqlCommand change.

EDIT

Please note that if there are a large number of copies of your program, it can lead to excessive load on the server. If your application is publicly available, this might not be a good idea.

Also note that it can fire an event in different threads, so you will need to use Control.BeginInvoke to update your interface.

Answer 2

You can use event notifications in SQL Server to raise events in .Net by letting you know that the data has changed. See the article below.

Eric

SQL Server Event Notification

Answer 3

With ASP.Net, you can cache query results in memory and configure the dependency that is logged using SQL Server. When something inside the data changes, the cache updates automatically. Perhaps studying this may point you in a good direction. http://msdn.microsoft.com/en-us/library/ms178604.aspx