How do I get a Windows Form client to update every time a SQL Server table changes?

Question

I have a form with a list that shows information from a database. I want the list to update in real time (or almost real time) every time something changes in the database. These are the three ways I can think of to accomplish this:

• Set up a timer on the client to check every few seconds: I know how to do this now, but it would involve making and closing a new connection to the database hundreds of times an hour, regardless of whether there was any change
• Build something sort of like a TCP/IP chat server, and every time a program updates the database it would also send a message to the TCP/IP server, which in turn would send a message to the client's form: I have no idea how to do this right now
• Create a web service that returns the date and time of when the last time the table was changed, and the client would compare that time to the last time the client updated: I could figure out how to build a web service, but I don't how to do this without making a connection to the database anyway

The second option doesn't seem like it would be very reliable, and the first seems like it would consume more resources than necessary. Is there some way to tell the client every time there is a change in the database without making a connection every few seconds, or is it not that big of a deal to make that many connections to a database?

Answer 1

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

EDIT:

Note that if there are large numbers of copies of your program running, it can generate excessive server load. If your app will be publicly available, it might not be a good idea.

Also note that it can fire the event on different threads, so you'll need to use Control.BeginInvoke to update your UI.