SQL Server 2008 notifications

Question

C#, VS 2005, SQL Server 2008

I have a database on a remote server. It has a table table1. Several clients work with it. Each client can change the content of table1. Each client must represent actual content of this table. So I need some algorithm to make sure the clients always represent the most current content of table1.

I know that SQL Server 2008 supports a notification mechanism (reporting services). How can I use it for my task ?

Answer 1

Use a cache invalidation mechanism around Query Notification. With QN each client builds a cache of its view of table1 and receives a notification when it's view of the data is stale and needs to be refreshed. See The Mysterious Notification for more details how this works. In your C# project this boils down to properly leveraging the SqlDependency class.