Get notifications on record table changes

Detect record table changes

One of the classic problems when writing software dealing with database is refreshing data.

Imagine a tool for display real time booking flight. After a first data access to retrieve records from database table, in order to maintain the cache up to date, further selects are needed over and over again. That is inefficient if the data rarely changes and it wastes resources and execution time on the database server.

Caching is one technique for minimizing demands on the database server. The data are queried once and stored in memory and the application then repeatedly accesses it. Occasionally, the cache is updated to refresh the data. The point is deciding when to update it. If we don’t do it often enough, users see old data; if we update too often, then we don’t optimally reduce our demand on the database.

Wouldn't it be better if was our database to instantly notify our application about record changes, avoiding us to execute a periodical SELECT to refresh our server cache?

Database table change notifications can help to solve this problem. But how monitor SQL Server table changes? Some options are:

• SQL Server Service Broker
• .NET SqlNotificationRequest
• .NET SqlDependency

All of them works based on a notifications infrastructure. The first two options require us a good T-SQL and database knoledge in order to create database objects as service broker and queue to monitor every change done on our records table and notify us about any record table change. Although the last one does not require us this low level knoledge, it presents a limitation: delivered notification does not report us any information about which record has been changed, forcing us to perform a further SELECT to refresh our cache.

Is it possible receive record table change notifications containing modified, deleted or inserted records in order to avoid another SELECT to mantains update our cache?

Monitor insert update delete table changes

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

SqlTableDependency 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.

Once installed SqlTableDependency nuget package, we can receive table record change notification containing current table values, audit for any:

• insert
• update
• delete

To be notify about record table changes, first of all we need to define our model object mapping the table structure we want to monitor.

Get real time notifications on record changes

Define a C# class to contains new record table values filled by table change notification. Basically, it maps the interested database table columns, specifing for whom columns the notification returns updated values. Supposing a table as:

TABLE [dbo].[Client]( 
	[Id],
	[FirstName],
	[SecondName],
	[Addess],
	[City],
	[Email],
	[Phone])

Our C# model defines the properties we'd like to have filled in with changed table values delivered from table notification:

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

It is possible to define only table columns we are interested to receive values by notification, simply secifing the a model mapper as show later.

Audit table changes with SqlTableDependency

This object builds SQL Server infrastructure to receive database notification for record change.

Create it passing database connection string and, in case naming between C# class and database table are differents, specify table name as second parameter. We can also specify a C# property / column table name mapper, when the namings are differents:

var map = new ModelToTableMapper<Customer>();
map.AddMapping(c => c.Surname, "SecondName").AddMapping(c => c.Name, "FirstName");

var dep = new SqlTableDependency<Customer>(_con, "Client", map))

Listen for table change

When we want to receive notification on table content modify, just subscribe to change event:

dep.OnChanged += Changed;

Notification contains values for insert, delete or update table change:

private void Changed(object sender, RecordChangedEventArgs<Customer> e)
{
    if (e.ChangeType != ChangeType.None)
    {
        var changedEntity = e.Entity;

        // Get record values from SQL Server notification
        Console.WriteLine("DML operation: " + e.ChangeType);
        Console.WriteLine("ID: " + changedEntity.Id);
        Console.WriteLine("Name: " + changedEntity.Name);
        Console.WriteLine("Surame: " + changedEntity.Surname);
    }
}

Start tracking record changes

Run the listener for receiving table change notifications for every DML operation executed on the monitored table:

dep.Start();

Dispose notification change listener

Call Stop method to remove all database objects created to receive table record change notifications:

dep.Stop();

This remove the database infrastructure to deliver table change notification.

Table change alert infrastructure

SqlTableDependency creates and disposed the following SQL Server object for table under monitor:

• Table trigger: put value record for insert, delete and update in SQL Server queue.
• Service broker: deliver notification messages.
• Queue: store notification messages.
• Contract: define notification message format.
• Messages type: specify notification message types.
• Stored procedure: queue activation.

---

---

Detect record table changes: Last edit on 10 November 2016 by Christian Del Bianco