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

Question

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 1

In the interests of completeness there are a couple of other solutions which (in my opinion) are more orthodox than solutions relying on the SqlDependency (and SqlTableDependency) classes. SqlDependency was originally designed to make refreshing distributed webserver caches easier, and so was built to a different set of requirements than if it were designed as an event producer.

There are broadly four options, some of which have not been covered here already:

• Change Tracking
• CDC
• Triggers to queues
• CLR

Change tracking

Source: https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server

Change tracking is a lightweight notification mechanism in SQL server. Basically, a database-wide version number is incremented with every change to any data. The version number is then written to the change tracking tables with a bit mask including the names of the columns which were changed. Note, the actual change is not persisted. The notification only contains the information that a particular data entity has changed. Further, because the change table versioning is cumulative, change notifications on individual items are not preserved and are overwritten by newer notifications. This means that if an entity changes twice, change tracking will only know about the most recent change.

In order to capture these changes in c#, polling must be used. The change tracking tables can be polled and each change inspected to see if is of interest. If it is of interest, it is necessary to then go directly to the data to retrieve the current state.

Change Data Capture

Source: https://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

Change data capture (CDC) is more powerful but most costly than change tracking. Change data capture will track and notify changes based on monitoring the database log. Because of this CDC has access to the actual data which has been changed, and keeps a record of all individual changes.

Similarly to change tracking, in order to capture these changes in c#, polling must be used. However, in the case of CDC, the polled information will contain the change details, so it's not strictly necessary to go back to the data itself.

Triggers to queues - this method is obsolete

This technique depends on triggers on the tables from which notifications are required. Each change will fire a trigger, and the trigger will write this information to a service broker queue. The queue can then be connected to via C# using the Service Broker Message Processor (sample in the link above).

Unlike change tracking or CDC, triggers to queues do not rely on polling and thereby provides realtime eventing.

CLR

This is a technique I have seen used, but I would not recommend it. Any solution which relies on the CLR to communicate externally is a hack at best. The CLR was designed to make writing complex data processing code easier by leveraging C#. It was not designed to wire in external dependencies like messaging libraries. Furthermore, CLR bound operations can break in clustered environments in unpredictable ways.

This said, it is fairly straightforward to set up, as all you need to do is register the messaging assembly with CLR and then you can call away using triggers or SQL jobs.

In summary...

It has always been a source of amazement to me that Microsoft has steadfastly refused to address this problem space. Eventing from database to code should be a built-in feature of the database product. Considering that Oracle Advanced Queuing combined with the ODP.net MessageAvailable event provided reliable database eventing to C# more than 10 years ago, this is woeful from MS.

The upshot of this is that none of the solutions listed to this question are very nice. They all have technical drawbacks and have a significant setup cost. Microsoft if you're listening, please sort out this sorry state of affairs.

Answer 2

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.

Answer 3

Generally, you'd use Service Broker

That is trigger -> queue -> application(s)

Edit, after seeing other answers:

FYI: "Query Notifications" is built on Service broker

Edit2:

More links

• Event Notification/Service broker
• Service Broker team