Audit SQL Server table change by monitor insert update delete and receive notifications

SqlTableDependency is a C# class used to receive notifications containing the modified record values when the content of a specified database table change: is a high-level implementation to access notifications feature on SQL Server following the Service Oriented Database Architecture.



SqlTableDependency is a high-level implementation to access table record change notifications from SQL Server.

This class allows you to create a dependency to detect changes on a database table. Every change will trigger a notification messages contains values for the updated,inserted or deleted record, avoid you to execute a further select to update your application data.

If you want to use record change notifications without paying attention to the underlying Service
Broker infrastructure, the SqlTableDependency class is your choice.

The SqlTableDependency class represents a notification dependency between an application and a SQL Server Table. When you use SqlTableDependency to get notifications, this componenet provides the low-level implementation creating the Queue and Service Broker. In this way SqlTableDependency class provides access to notifications without knowing anything about the underlying Service Broker infrastructure. Once a record changed happens, SqlTableDependency's event subscribers are notified.

Basically, it is an enhancement of .NET SqlDepenency with the advantage of send events containing values for the record inserted, changed or deleted, as well as the DML operation (insert/delete/update) executed on the table. This is the real difference with. NET SqlDepenency: this class, in fact, does not tell you what data was changed on the database.

SqlTableDependency supports:
  • C# generic pattern
  • IDisposable pattern
  • Management of multiple changes contemptuously (insert/update/delete/merge involving more records):
INSERT INTO dbo.Client ([First Name], [Second Name]) 
VALUES ('Tom', 'Gate'), ('Sam', 'Bezh')
DELETE FROM dbo.Client WHERE ID > 121 AND ID < 931
UPDATE dbo.Client SET [First Name] = 'new value' WHERE ID > 24
  • Transaction: when data change operations are executed under transaction, the SqlTableDependency change event is sent only when the commit is called.
Note: When you use notifications, you must be sure to enable Service Broker for the database. To do that you can use the following command:

ALTER DATABASE MyDatabase SET ENABLE_BROKER

User's Grants

In case user specified in connection string is not DBO or has not db_owner role, he must have the following GRANT permissions:
  • ALTER
  • CONNECT
  • CONTROL
  • CREATE CONTRACT
  • CREATE MESSAGE TYPE
  • CREATE PROCEDURE
  • CREATE QUEUE
  • CREATE SERVICE
  • EXECUTE
  • SELECT
  • SUBSCRIBE QUERY NOTIFICATIONS
  • VIEW DATABASE STATE
  • VIEW DEFINITION
Under The Hood

Assuming we want monitor the Customer table contents, 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)
Figure 1. Database Object created for send notifications


How to use

In the following video you can see a very basic demonstration using a Model without Code First Data Annotations.



Create a SqlTableDependency object and register to receive notifications via an event handler. Table 1 shows the members of the SqlTableDependency class.

Table 1. Members of the SqlTableDependency Class
Member Description
Start() This method starts the listener for receiving change notifications.
Stop() This method stops the listener.
OnChange This event occurs when a notification is received for any change done on table content.

To set up a dependency, you have to do the following:
  • Create an instance of SqlTableDependency passing the connection string and the table name to be monitored.
  • Subscribe to SqlTableDependency's OnChange event.
  • Call Start() method to start receiving change notifications.
  • Call Stop() method to end receiving notifications. This operation will also destroy all database objects created for receiving notifications.
Let’s take a detailed look at each of these steps. Supposing the following database table:

CREATE TABLE [dbo].[Client](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[First Name] [nvarchar](50) NOT NULL,
	[Second Name] [nvarchar](50) NOT NULL)

If a C# client needs be notified every time a record is modified, deleted or changed, you define the model containing the properties you'd like to have filled in with changed table values:

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

The model can avoid to define all table columns if you are not interested in some value.

Create the SqlTableDependency object passing the connection string and table name. Then create an event handler for SqlTableDependency's Changed event:

using System;
using TableDependency.SqlClient;
using TableDependency.Enums;
using TableDependency.Events;

class Program
{
   var _con= "data source=.; initial catalog=MyDB; integrated security=True";
   
   static void Main()
   {
       var mapper = new ModelToTableMapper<Customer>();
       mapper.AddMapping(c => c.Surname, "Second Name");
       mapper.AddMapping(c => c.Name, "First Name");

       using (var dep = new SqlTableDependency<Customer>(_con, "Client", mapper))
       {
           dep.OnChanged += Changed;
           dep.Start();

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

           dep.Stop();
        }
   }

   static void Changed(object sender, RecordChangedEventArgs<Customer> 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("Surame: " + changedEntity.Surname);
       }
   }
}

Now you are ready to receive notifications. Open SQL Server management studio and insert, update or delete some record in the Customer table:

Figure 2. Events reception


As you can see, the difference from the .NET SqlDependency is that SqlTableDependency send an event containing a T object filled with table changed data.

Table 2. Properties of the RecordChangedEventArgs<T> Class
Property Description
ChangeType Gets a value of the ChangeType enum that indicates the reason for the notification event (insert/update/delete).
Entity This property return an entity <T> containing changed table's record.

More examples
  • 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.
Useful link NuGet package Contribute

Please, feel free to help and contribute with this project adding your comments, issues or bugs found.

If you find SqlTableDependency useful, please consider making a donation.

Donate to TableDependency

Contacts

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

Last edited Mar 26 at 11:08 AM by delbianco, version 76