Audit, monitor and receive notifications on table change

SqlTableDependency is a high-level C# component to used to audit, monitor and receive notifications on SQL Server's record table change.

For any record table change, insert update or delete, a notification containing values for the record inserted, changed or deleted is received from SqlTableDependency. This notification contains the update values int the database table.

Compared to Microsoft ADO.NET SqlDependency class, this tracking change system has the advantage of avoid a database select to retrieve updated table record state, because this latest table status is delivered by the received notification.

Track record table change

If we want get alert about record table changes 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 changes saving us from having to continuously re-query the database to get new values.

SqlTableDependency's record change audit, provides the low-level implementation to receive database notifications creating SQL Server trigger, queue and service broker that immediately notify us when any record table changes happens.

For any record change, SqlTableDependency's event handler will get a notification containing modified table record values as well as the insert, update, delete operation type executed on our table.

Read more about SqlTableDependency.

Few steps to get alert on table insert update delete

1. Install SqlTableDependency using:



2. Write your model defining interested properties:

public class Customers
{
    public int Id { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
}

3. Create SqlTableDependency:

using (var dep = new SqlTableDependency<Customers>(connectionString))
{
    dep.OnChanged += Changed;
    dep.Start();

    Console.WriteLine(@"Waiting for receiving notifications...");
    Console.WriteLine(@"Press a key to stop");
    Console.ReadKey();
}

4. Specify an table record change event handler:

private static void Changed(object sender, RecordChangedEventArgs<Customers> e)
{
    Console.WriteLine(Environment.NewLine);

    if (e.ChangeType != ChangeType.None)
    {
        var changedEntity = e.Entity;
        Console.WriteLine(@"DML operation: " + e.ChangeType);
        Console.WriteLine(@"CustomerID:    " + changedEntity.Id);
        Console.WriteLine(@"ContactTitle:  " + changedEntity.ContactTitle);
        Console.WriteLine(@"ContactName:   " + changedEntity.ContactName);
    }
}

Done!

Read more about SqlTableDependency.

Detect record table changes use cases

With SqlTableDependency it is possible:
  • define only table columns we are interested to receive values by notification, simply secifing the a model mapper as show later
  • specify for which record table change we want to receive a notification
  • specify for which column table change we want to receive a notification
  • and more...
Use cases examples can be found in this page.

Audit table changes examples

Here are two examples of application getting notification on record table change. After downoad the example, plese remember to update SqlTableDependency nuget package:
  • Monitor table change with WPF and WCF: This example show how to keep up to date a grid containing some stocks data. That grid has been automatically updated whenever a record change using database notifications. This notification contains new values for the modified table record.
  • Monitor table change with MVC, SignalR and jQuery: This example show how to keep up to date a table containing some stocks data. That table has been automatically updated whenever a record change using database notifications. This notification contains new values for the modified table record.
  • Monitor table change with MVC, SignalR and Knockout JS: This example show how to refresh client web browsers used to book flight tickets. Those terminals have to be update 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.

Feature Comparison

Feature SqlTableDependency .NET SqlDependency
Implementing <T>
Supporting VIEW
Supporting Data Annotation on model
Table columns list
Returning modified record's values
Returning inserted record's values
Returning deleted record's values
Returning DML type (DELETE-INSERT-UPDATE)
Specifies interested DML to listen for
Specifies column's change triggering notification
Maintaining database QUEUE after dispose
Supporting XML, NVARCHAR(MAX), BYNARY
Supporting TEXT, NTEXT, and IMAGE
Notification only on real data change
WHERE condition (1)
Automatic dispose
Supporting transaction
Avoid re-register after first notification

(1) Visit SqlTableDependency Filter for more info.

Listen for table change Load Test and Performance

SqlTableDependency support notifications triggered by DML executed every 50 milliseconds. Test realized with two client applications concurrently executing 10.000 random insert/update/operation on same table.
Contribute

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
Donate

TableDependency, SqlTableDependency and OracleTableDependency are personal open source projects. Started in 2015, I have put hundreds of hours adding new features, enhancing and fixes, with the goal to make them a usefull and a user friendly component. I need your help to achieve this.

If you find TableDependency and SqlTableDependency helpful, please consider making a donation.

Donate to TableDependency
Contacts

Christian Del Bianco
christian.delbianco@gmail.com
christian.delbianco

Last edited Sun at 5:18 PM by delbianco, version 181