Using SqlDependency in a Windows Application

Question

Hi,

I am trying to implement test application which will monitor all changes in table (MS SQL Server 2008). I have followed all steps described HERE, but strange thing is happening ... it goes for endless loop. When GetData calls adapter.Fill(dataToWatch, tableName); the dependency_OnChange event is raised which in its turn calls GetData, so it goes for endless loop. The only thing I have changed is the SQL query from the sample. It looks like this Select ID, Col1, Col2 from Table. But the main issue is not in endless loop. The issue is that when I update any data in table event is not raised.

Please let me know does anyone tries the sample from MSDN or can give a link where I get a working one or just somehow help me the problem.

Thank you.

Answer 1

The sample omits some minor details, like checking for the notification type and source. In the dependency_OnChange(object sender, SqlNotificationEventArgs e) you must check the SqlNotificationEventArgs parameter. You only got a data change notification if Source is Data and Type is Change. Otherwise you probably got a notification that the query is invalid for notifications, and re-submitting it will get you into a loop. The Info will contain details why the query is invalid for notifications.

The criteria a query needs to qualify for notifications are described at Creating a Query for Notification. Some more details how this feature works are available at The Mysterious Notification.