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

User · Answer

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://docs.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

Source: https://code.msdn.microsoft.com/Service-Broker-Message-e81c4316

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.

User · Answer

SqlDependency doesn t watch the database it watches the SqlCommand you specify so if you are trying to lets say insert values into the database in 1 project and capture that event in another project it won t work because the event was from the SqlCommand from the 1   project not the database because when you create an SqlDependency you link it to a SqlCommand and only when that command from that project is used does it create a Change event

User · Answer

This isn t exactly a notification but in the title you say monitor and this can fit that scenario  Using the SQL Server timestamp column can allow you to easily see any changes  that still persist  between queries  The SQL Server timestamp column type is badly named in my opinion as it is not related to time at all  it s a database wide value that auto increments on any insert or update  You can select Max timestamp  in a table you are after or return the timestamp from the row you just inserted then just select where timestamp  gt  storedTimestamp  this will give you all the results that have been updated or inserted between those times  As it s a database wide value too you can use your stored timestamp to check any table has had data written to it since you last checked updated your stored timestamp

User · Answer

looks like bad architecture all the way  also you have not specified the type of app you need to notify to  web app   console app   winforms   service etc etc   nevertheless  to answer your question  there are multiple ways of solving this  you could use   1  timestamps if you were just interested in ensuring the next set of updates from the second app dont conflict with the updates from the first app  2  sql dependency object - see http   msdn microsoft com en-us library system data sqlclient sqldependency aspx for more info  3  a custom push notification service which multiple clients  web   winform   service  can subscribe to and get notified on changes  in short  you need to use the simplest and easiest and cheapest  in terms of efforts  solution based on how complex your notification requirements are and for what purpose you need to use them  dont try to build an overly complex notification system if a simple data concurrency is your only requirement  in that case go for a simple timestamp based solution

User · Answer

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

User · Answer

Use SqlTableDependency  It is a c  component raising events when a record is changes  You can find others detail at  https   github com christiandelbianco monitor-table-change-with-sqltabledependency  It is similat to  NET SqlDependency except that SqlTableDependency raise events containing modified   deleted or updated database table values   string conString    data source   initial catalog myDB integrated security True    using var tableDependency   new SqlTableDependency lt Customers gt  conString         tableDependency OnChanged    TableDependency Changed      tableDependency Start         Console WriteLine  Waiting for receiving notifications           Console WriteLine  Press a key to stop        Console ReadKey              void TableDependency Changed object sender  RecordChangedEventArgs lt Customers gt  e        if  e ChangeType    ChangeType None                var changedEntity   e Entity          Console WriteLine  DML operation      e ChangeType           Console WriteLine  ID      changedEntity Id           Console WriteLine  Name      changedEntity Name           Console WriteLine  Surname      changedEntity Surname

User · Answer

Since SQL Server 2005 you have the option of using Query Notifications  which can be leveraged by ADO NET see  http   msdn microsoft com en-us library t9x04ed2 aspx

User · Answer

Another  very simple way of monitoring tables is table versioning  The system is proven working in constructions such as DNS synchronization  To make it work you create a table containing table names and table versions as decimal or bigint In each table that you need monitored  create trigger on insert  update and delete that will increment appropriate table version in versioning table when executed  If you expect any of the monitored tables to be altered often  you need to provision for version reusing  Finally  in your application  every time you query monitored table  you also query its version and store it  When you go to alter the monitored table from your app  you first query its current version and process the change only if the version is unchanged  You can have stored proc on sql server do that work for you  This is extremely simple but proven solid solution  It has specific functional use  to ensure data consistency  and is light on resources  you do not raise brokered events that you would not watch for  but needs application to actively check for changes rather than passively wait for event to happen

User · Answer

Be careful using SqlDependency class - it has problems with memory leaks    Just use a cross-platform   NET 3 5   NET Core compatible and open source solution - SqlDependencyEx  You can get notifications as well as data that was changed  you can access it through properties in notification event object   You can also tack DELETE UPDATE INSERT operations separately or together   Here is an example of how easy it is to use SqlDependencyEx   int changesReceived   0  using  SqlDependencyEx sqlDependency   new SqlDependencyEx            TEST CONNECTION STRING  TEST DATABASE NAME  TEST TABLE NAME          sqlDependency TableChanged     o  e    gt  changesReceived        sqlDependency Start            Make table changes      MakeTableInsertDeleteChanges changesCount           Wait a little bit to receive all changes      Thread Sleep 1000      Assert AreEqual changesCount  changesReceived     Please follow the links for details  This component was tested in many enterprise-level applications and proven to be reliable  Hope this helps

User · Answer

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

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

Examples related to c#

Examples related to sql-server

Examples related to monitoring