Monitor and receive notifications on record table change

SqlTableDependency is a high-level C# component used to audit, monitor and receive notifications on SQL Server's record table changes. For any record table change, as insert, update or delete operation, a notification containing values for the record changed is delivered to SqlTableDependency. This notification contains insert, update or delete record values.

This table record tracking change system has the advantage to avoid a select to retrieve updated table record, because the updated table values record is delivered by notification.

Get record table change

If we want get alert on record table change without paying attention to the underlying SQL Server infrastructure then SqlTableDependency's record table change notifications will do that for us. Using notifications, an application can detect table record change saving us from having to continuously re-query the database to get new values: for any record table change, SqlTableDependency's event handler get a notification containing modified table record values as well as the INSERT, UPDATE, DELETE operation type executed on database table.

As example, let's assume we are interested to receive record table changes for the following database table:

Let's start installing SqlTableDependency using:

We now define a C# model mapping table columns we are interested: these properties will be populated with the values resulting from any INSERT, DELETE or UPDATE record table change operation. We do not need to specify all table columns but just the ones we are interested:

public class Customer
{
 public int Id { get; set; }
 public string Name { get; set; }
 public string Surname { get; set; }
}

Model properties can have different name from table columns. We'll see later how to establish a mapping between model properties and table columns with different name.

Now create SqlTableDependency instance passing the connection string and table name (database table name is necessary only if the C# model has a name that is different from the database table name). Then create an event handler for SqlTableDependency's Changed event:

public class Program
{
 private static string _con = "data source=.; initial catalog=MyDB; integrated security=True";
   
 public static void Main()
 {
  // The mapper object is used to map model properties 
  // that do not have a corresponding table column name.
  // In case all properties of your model have same name 
  // of table columns, you can avoid to use the mapper.
  var mapper = new ModelToTableMapper<Customer>();
  mapper.AddMapping(c => c.Surname, "Second Name");
  mapper.AddMapping(c => c.Name, "First Name");

  // Here - as second parameter - we pass table name: 
  // this is necessary only if the model name is different from table name 
  // (in our case we have Customer vs Customers). 
  // If needed, you can also specifiy schema name.
  using (var dep = new SqlTableDependency<Customer>(_con, "Customers", mapper: mapper));
  {
   dep.OnChanged += Changed;
   dep.Start();

   Console.WriteLine("Press a key to exit");
   Console.ReadKey();

   dep.Stop();
  } 
 }

 public static void Changed(object sender, RecordChangedEventArgs<Customer> e)
 {
  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);
 }
}

Done! Now you are ready to receive record table change notifications:

Monitor record table change examples and use cases

To see SqlTableDependency in action, check the following online long running test. Here, SqlTableDependency is tested continuously using a thread that every five seconds perform an update record table change. SqlTableDependency monitor this record table change and get a notification containing new update record table values.

Also, here are some examples of applications getting notification on record table change. After downloading the example, please remember to update SqlTableDependency nuget package:

• Monitor table change with Blazor: This example uses .NET CORE 3.0 Blazor (server side) to create a single page application that makes real-time update of its content on database record changes.
• Monitor table change with WPF and WCF: This example shows how to refresh a DataGrid of stock data. The grid will be updated whenever a record table change occurs. The notification event contains new values for the modified table record.
• Monitor table change with MVC, SignalR and jQuery: This example shows how to refresh a HTML table containing stock values. The HTML table will be updated whenever a record table change occurs. Notification event contains new values for the modified table record.
• Monitor table change with MVC, SignalR and Knockout JS: This example shows how to refresh client web browsers used to book flight tickets. Those terminals must be updated as soon as the availability change and the Web application must take the initiative of sending this information to clients instead of waiting for the client to request it.

This section reports some use case examples:

• Model and properties with same name of table and columns.
• Code First Data Annotations to map model with database table.
• Explicit database table name.
• Custom map between model property and table column using ModelToTableMapper.
• Specify for which properties we want receive notification using UpdateOfModel mapper.
• Filter notification by change type.
• Get Errors.
• Logging.
• Get Status.
• Apply filter based on WHERE condition.
• Include old values.

How Track record table change is done

SqlTableDependency's record change audit, provides the low-level implementation to receive database record table change notifications creating SQL Server triggers, queues and service broker that immediately notifies your application when a record table change happens.

Assuming we want to monitor the Customer table content, we create a SqlTableDependency object specifying the Customer table and the following database objects will be generated:

• Message types
• Contract
• Queue
• Service Broker
• Trigger on table to be monitored
• Stored procedure to clean up the created objects in case the application exits abruptly (that is, when the application terminate without disposing the SqlTableDependency object)

Remark about record table change system

The Start(int timeOut = 120, int watchDogTimeOut = 180) method starts the listener to receive record table change notifications. The watchDogTimeOut parameter specifies the amount of time in seconds for the watch dog system.

After calling the Stop() method, record table change notifications are not longer delivered. Database objects created by SqlTableDependency will be deleted.

It is a good practice - when possible - wrap SqlTableDependency within a using statement or alternatively in a try catch block: when the application will stop, this is enough to remove the SqlTableDependency infrastructure (Trigger, Service Broker, Queue, Contract, Messages type and Stored Procedure) automatically.

In any case, when the application exits abruptly – that is by not calling the Stop() and/or Dispose() method - we need a way to cleaning up the SqlTableDependency infrastructure. The Start() method takes an optional parameter watchDogTimeOut. If there are no listeners waiting for notifications, the SqlTableDependency infrastructure will be removed after this period of time. The default value of watchDogTimeOut is 180 seconds.

There is a common scenario that could trigger the watchdog: debugging. During development, you often spend several minutes inside the debugger before you move on to the next step. Please make sure to increase watchDogTimeOut when you debug an application, otherwise you will experience an unexpected destruction of database objects in the middle of your debugging activity.

Audit record table change requirements and info

• SQL Server 2008 R2 or latest versions (please see note about Compatibility Level and Database Version).
• .NET Framewrok 4.5.1 or latest versions / .NET CORE 2.0 or latest versions.
• When database connection has been lost, there is no way to re-connect SqlTableDependency instance to its queue. A new instance of SqlTableDependency is needed to get record table change notifications again.
• Windows service using SqlTableDependency must not goes to SLEEP mode or IDLE state. Sleep mode blocks SqlTableDependency code and this result in running the database watch dog that drops all SqlTableDependency's db objects (please see https://stackoverflow.com/questions/6302185/how-to-prevent-windows-from-entering-idle-state).
• Database Backup and Restore: restoring SqlTableDependency's db objects, it does not work.

To use notifications, you must be sure to enable Service Broker for the database. To do this run the SQL command:

ALTER DATABASE MyDatabase SET ENABLE_BROKER

In case the user specified in the connection string is not database Administrator, db owner or neither has db_owner role, please make sure to GRANT the following permissions to your login user:

• ALTER
• CONNECT
• CONTROL
• CREATE CONTRACT
• CREATE MESSAGE TYPE
• CREATE PROCEDURE
• CREATE QUEUE
• CREATE SERVICE
• EXECUTE
• SELECT
• SUBSCRIBE QUERY NOTIFICATIONS
• VIEW DATABASE STATE
• VIEW DEFINITION

It is possible skip permissions test done by SqlTableDependency setting executeUserPermissionCheck constructor parameter to false. Nevertheless a SQL server exception will be thrown if user have not sufficient permissions.

In case you specify SqlTableDependency's QueueExecuteAs property (default value is "SELF"), can also be necessary set TRUSTWORTHY database property using:

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON

Note about Compatibility Level and Database Version for tracking record changes

Please, check how David Green solved this problem: https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency/wiki/Contributors

From time to time, I receive bugs reporting issue like "I not detect/receive any record table change notification". Assuming that you are using a logic with enough grants, one of the possible cause of this missing table record change notification, is due to Database compatibility version. Even if your SQL Server instance is SQL Server 2008 R2 or latest versions, can be that your Databasehas been created using an old SQL Server version, for example SQL Server 2005. To reproduce this issue, you can download Northwind.mdf file and then attach it to your SQL Server 2008 R2 (or greater) instance. Running SqlTableDependency against it, no exception is raised as well as no notification on record table change is detected.

In order to discover your database compatibility version, you can use the following SQL script (see details on http://jongurgul.com/blog/database-created-version-internal-database-version-dbi_createversion/).

USE <your db>

DECLARE @DBINFO TABLE ([ParentObject] VARCHAR(60),[Object] VARCHAR(60),[Field] VARCHAR(30),[VALUE] VARCHAR(4000))
INSERT INTO @DBINFO
EXECUTE sp_executesql N'DBCC DBINFO WITH TABLERESULTS'
SELECT [Field]
,[VALUE]
,CASE
WHEN [VALUE] = 515 THEN 'SQL 7'
WHEN [VALUE] = 539 THEN 'SQL 2000'
WHEN [VALUE] IN (611,612) THEN 'SQL 2005'
WHEN [VALUE] = 655 THEN 'SQL 2008'
WHEN [VALUE] = 661 THEN 'SQL 2008R2'
WHEN [VALUE] = 706 THEN 'SQL 2012'
WHEN [VALUE] = 782 THEN 'SQL 2014'
WHEN [VALUE] = 852 THEN 'SQL 2016'
WHEN [VALUE] > 852 THEN '> SQL 2016'
ELSE '?'
END [SQLVersion]
FROM @DBINFO
WHERE [Field] IN ('dbi_createversion','dbi_version')

Executing this script on Northwind database you get:

Executing this script on DB created by SQL Server 2008 R2 instance (database name TableDependencyDB), the result is:

So, even if your SQL Server instance is 2008 R2 or greater, DB compatibility level (VALUE column) is fundamental to receive record table change notifications.

Not supported SQL Server table column types

Following SQL Server columns types are not supported by SqlTableDepdency:

• XML
• IMAGE
• TEXT/NTEXT
• STRUCTURED
• GEOGRAPHY
• GEOMETRY
• HIERARCHYID
• SQL_VARIANT

Limitations

SqlTableDependency does not consider an empty string different from a string containing only spaces; example: '' and ' ' are considered eguals. This means that in there is case of update from '' to ' ' - and vice versa - this change it will not be notified. Same is true for NULL and empty string. Please check https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces.

SqlTableDependency works with traditional disk-based tables: it does not works with In-Memory OLTP tables.

Difference between SqlTableDependency and SqlDependency from ADO.NET

Functionalities comparison between Microsoft ADO.NET SqlDependency and SqlTableDependency:

Functionality	SqlTableDependecy	SqlDependency
View
Join multiple tables
Where
Generic
Notification containing updated values
Notification containing old values
Notification only on insert
Notification only on update
Notification only on delete
Notification only when specific column is changes

Useful link and tips

• https://sqlrus.com/2014/10/compatibility-level-vs-database-version/
• https://stackoverflow.com/questions/41169144/sqltabledependency-onchange-event-not-fired
• https://stackoverflow.com/questions/11383145/sql-server-2008-service-broker-tutorial-cannot-receive-the-message-exception
• Deleting multiple records then inserting using sql bulk copy, only deleted change events are raised: to solve this problem, set SqlBulkCopyOptions.FireTriggers. Thanks to Ashraf Ghorabi!

Contributors

Open-source software (OSS) is a type of computer software in which source code is released under a license in which the copyright holder grants users the rights to study, change, and distribute the software to anyone and for any purpose.Open-source software may be developed in a collaborative public manner. Please, feel free to help and contribute with this project adding your comments, issues or bugs found as well as proposing fix and enhancements. See contributors.