Menu
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.
361
Right click on the table you want to track changes. Click Properties, click Change Tracking, then in the right pane set Change Tracking to TRUE.
Inspect the "TextData" column for the events: CREATE DATABASE, DROP DATABASE, ALTER DATABASE in order to know what database was changed. To find out who changed it and when it was changed, refer to the "LoginName" and "StartTime" columns respectively.
Launch Activity Monitor and Object Explorer on startupFrom the Tools menu, select Options. In the Options dialog box, expand Environment, and then select Startup. From the At startup drop-down list, select Open Object Explorer and Activity Monitor. Select OK.
SqlDependency allows you to receive notifications when the original data in the database changes so that the cache can be refreshed. To set up a dependency, you need to associate a SqlDependency object to one or more SqlCommand objects. To receive notifications, you need to subscribe to the OnChange event.
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
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.
182
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.
64
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
19
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
