How to monitor SQL Server table changes by using c#?

ToDayIsNow picture ToDayIsNow · Mar 13, 2011 · Viewed 115.7k times · Source

I have more than one application accessing the same DB and I need to get notified if one of these apps change anything (update, insert) in a certain table.

Database and apps are not in the same server.

Answer

Jaroslav Jandek picture Jaroslav Jandek · Mar 13, 2011

You can use the SqlDependency Class. Its intended use is mostly for ASP.NET pages (low number of client notifications).

ALTER DATABASE UrDb SET ENABLE_BROKER

Implement the OnChange event to get notified:

void OnChange(object sender, SqlNotificationEventArgs e)

And in code:

SqlCommand cmd = ...
cmd.Notification = null;

SqlDependency dependency = new SqlDependency(cmd);

dependency.OnChange += OnChange;

It uses the Service Broker (a message-based communication platform) to receive messages from the database engine.