Receive Notifications when a Table Record Changes with C#

The SQLDependency object represents a query notification dependency between an application and an instance of SQL Server. An application can create a SqlDependency object and register to receive notifications via the OnChangeEventHandler event handler every time a record is changed.

However, what it cannot do, is returning the values for the inserted, modified or deleted record.

So, supposing our application has a cache containing record monitored from SqlDependency, for every notification, in order to refresh the cache we have to execute again our select.

SqlTableDependency

SqlTableDependency is a generic C# component used to receive events containing the values for the modified, inserted or delete record. So, supposing our application has a cache filled with data got from a specific table, when its content change, we will receive an event containing a C# object with all property filled with column table values.

How it Works

When instantiated, SqlTableDependency create for us, a series of database objects, used to monitor a predefined table. The main object are:

1. Queue: containg a message with the modified record values.

2. Service Broker: insert the message in the queue.

3. Table Trigger: for each insert, update or delete operation, prepare the an XML message and use the Service Broker to insert it in the queue.

Watch Dog Time Out

SqlTableDependency implement the IDisposable interface, in order to remove the database object created. Infact it is a good practice is to wrap SqlTableDependency within a using statement or, alternatively, call the Stop() method once we do not need any more notifications. So, when the application will not disconnect abruptly, this approach is enough to remove the SqlTableDependency infrastructure (Trigger, Service Broker service, Queue).

However, when the application exits abruptly – that is not calling the Stop() method or not implementing the using statement - we need a way for cleaning up the SqlTableDependency infrastructure. The Start() method, has watchDogTimeOut optional parameter used to remove all the database objects. Its default value is 180 seconds: after this amount of time, if there are no listeners waiting for notifications, the SqlTableDependency infrastructure will be removed.

How to Use

In this example I created a very simple WPF application with a grid, simulating some stocks value.

Let's start assuming the following database table:

CREATE TABLE [dbo].[Stocks](
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
[Price] [decimal](18, 0) NULL)
GO
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'MCD', N'McDonald Corp', CAST(333 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'NKE', N'Nike Inc', CAST(240 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'DIS', N'Walt Disney Co', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'UTX', N'United Technologies Corp', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'MSFT', N'Microsoft Corp', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'PFE', N'Pfizer Inc', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'INTC', N'Intel Corp', CAST(130 AS Decimal(18, 0)))
INSERT [dbo].[Stocks] ([Code], [Name], [Price]) VALUES (N'KO', N'Coca Cola Co', CAST(130 AS Decimal(18, 0)))
GO

This table is constantly updated with new stock values. We want keep update our grid without use of any polling system. So, what we need is a notification coming from the database every time a record in the table has been changed. 

We install the SqlTableDependency package from visual studio using the following command:

Now we define a C# model mapping the interested table columns.

public class Stock
{
    public decimal Price { get; set; }
    public string Symbol { get; set; }
    public string Name { get; set; }
}

As you can see, model's properties name can have a different table column name. In this case, we need a mapper to correclty bind the values:

var mapper = new ModelToTableMapper<Stock>();
mapper.AddMapping(model => model.Symbol, "Code");

Finally we create our SqlTableDependency:

_dependency = new SqlTableDependency<Stock>(_connectionString, "Stocks", mapper);
_dependency.OnChanged += _dependency_OnChanged;
_dependency.OnError += _dependency_OnError;
_dependency.Start();

Below the complete code:

public partial class Window1 : Window
{
    private IList<Stock> _stocks;
    private readonly string _connectionString = "data source=.;initial catalog=TableDependencyDB;integrated security=True";
    private readonly SqlTableDependency<Stock> _dependency;
    public Window1()
    {
        this.InitializeComponent();
        this.McDataGrid.ItemsSource = LoadCollectionData();
        this.Closing += Window1_Closing;

        var mapper = new ModelToTableMapper<Stock>();
        mapper.AddMapping(model => model.Symbol, "Code");

        _dependency = new SqlTableDependency<Stock>(_connectionString, "Stocks", mapper);
        _dependency.OnChanged += _dependency_OnChanged;
        _dependency.OnError += _dependency_OnError;
        _dependency.Start();
    }

    private void Window1_Closing(object sender, System.ComponentModel.CancelEventArgs e)
    {
        _dependency.Stop();
    }

    private void _dependency_OnError(object sender, TableDependency.EventArgs.ErrorEventArgs e)
    {
        throw e.Error;
    }

    private void _dependency_OnChanged(object sender, TableDependency.EventArgs.RecordChangedEventArgs<Stock> e)
    {
        if (_stocks != null)
        {
            if (e.ChangeType != ChangeType.None)
            {
                switch (e.ChangeType)
                {
                    case ChangeType.Delete:
                        _stocks.Remove(_stocks.FirstOrDefault(c => c.Symbol == e.Entity.Symbol));
                        break;
                    case ChangeType.Insert:
                        _stocks.Add(e.Entity);
                        break;
                    case ChangeType.Update:
                        var customerIndex = _stocks.IndexOf(_stocks.FirstOrDefault(c => c.Symbol == e.Entity.Symbol));
                        if (customerIndex >= 0) _stocks[customerIndex] = e.Entity;
                        break;
                }

                this.McDataGrid.Dispatcher.Invoke(DispatcherPriority.Background, new Action(() =>
                {
                    this.McDataGrid.Items.Refresh();
                }));
            }
        }
    }

    private IEnumerable<Stock> LoadCollectionData()
    {
        _stocks = new List<Stock>();

        using (var sqlConnection = new SqlConnection(_connectionString))
        {
            sqlConnection.Open();
            using (var sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandText = "SELECT * FROM [Stocks]";

                using (var sqlDataReader = sqlCommand.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        var code = sqlDataReader.GetString(sqlDataReader.GetOrdinal("Code"));
                        var name = sqlDataReader.GetString(sqlDataReader.GetOrdinal("Name"));
                        var price = sqlDataReader.GetDecimal(sqlDataReader.GetOrdinal("Price"));

                        _stocks.Add(new Stock { Symbol = code, Name = name, Price = price });
                    }
                }
            }
        }

        return _stocks;
    }
}

As you can see from the code, only an initial select is performed to retrieve and populate the grid. Subsequent updates are done using the SqlTableDependency event handler: its receives a model object containing info about the record changed that we use to update our cache.