SQL Server notifying running .NET program of database updates

I have a database which can be updated from several instances of a C# .NET program. I wish to have each of those programs notified when rows in a table change.

An obvious (but possibly quite wrong) approach might be to register a callback function within the .NET program with the CLR Stored Trigger in the SQL Server, so that it gets called when the trigger fires.

Is this the correct/possible way, or is there a better alternative?

Polling the table for changes doesn't seem like a very efficient mechanism.

Thanks

July 12th, 2013 3:05pm

How would you notify your .NET programs when the trigger fires? A simple callback wouldn't do it. You'd have to do something within your trigger that "talks" to your clients.

There is a built-in feature for this called query notifications that uses internally fired events to and service broker to communicate with clients. It does have some limitations and "rough edges" though. Look for SqlDependency in the SqlClient provider.

Cheers, Bob

Free Windows Admin Tool Kit Click here and download it now
July 12th, 2013 6:26pm

I suggest you look at the service broker documentation.  Your trigger posts a message about each update, the service broker dispatches the message to be "handled".  Note that the link below is to the prior version of sql server since the 2012 BOL contains only information about updated functionality (and not the overview discussion).

service broker overview

July 12th, 2013 8:42pm

The name of the feature that I'm referring to a "Query Notifications".

Cheers, Bob

Free Windows Admin Tool Kit Click here and download it now
July 19th, 2013 4:26pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics