This project is read-only.

Filter notifications for SqlTableDepenceny

SqlTableDependency is a high-level C# component to used to audit, monitor and receive notifications on SQL Server's record table change. Notification on record table chage can be filter with a WHERE contidion, in order to specify which record change information we will receive.


How to filter record table change nofitications

Assuming we want to monitor change for the following database table:


Collecting update record table values in this C# model:

public class Product
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public int ItemsInStock { get; set; }       
}

We have two possibilities to filter nofification:
  • using SqlTableDependencyFilter nuget package
  • implement ITableDependencyFilter interface

Apply filter using SqlTableDependencyFilter

First of all, install:



Notification can be filtered applying a WHERE condition using a lamba expression. In thie example, we want receive an alert every time the Quantity for a table record with CategoryId 1 or 2 is updated with a value lower that 10:

public enum Category
{
    Food = 1,
    Drink = 2,
    Dessert = 3
}

static void Main(string[] args)
{
    var connectionString = ConfigurationManager
        .ConnectionStrings["SqlServerConnectionString"]
        .ConnectionString;

    // Because our model has a property that does not match table column name, 
    // we need to map C# Model PROPERTY <--> Database Table Column Name
    var mapper = new ModelToTableMapper<Product>();
    mapper.AddMapping(c => c.ItemsInStock, "Quantity");

    // Define WHERE filter specifing the WHERE condition
    // We also pass the mapper defined above as last contructor's parameter
    Expression<Func<Product, bool>> expression = p => 
        (p.CategoryId == (int)Category.Food || p.CategoryId == (int)Category.Drink) 
        && p.ItemsInStock <= 10;

    ITableDependencyFilter whereCondition = new SqlTableDependencyFilter<Product>(
        expression, 
        mapper);

    using (var dep = new SqlTableDependency<Product>(
        connectionString, 
        "Products", 
        mapper: mapper, 
        filter: whereCondition))
    {
        dep.OnChanged += Changed;
        dep.Start();

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

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

    var changedEntity = e.Entity;
    Console.WriteLine(@"DML operation: " + e.ChangeType);
    Console.WriteLine(@"CustomerID:    " + changedEntity.Id);
    Console.WriteLine(@"CategoryId:    " + changedEntity.CategoryId);
    Console.WriteLine(@"Name:          " + changedEntity.Name);
    Console.WriteLine(@"Quantity:      " + changedEntity.ItemsInStock);
}

Implement your own ITableDependencyFilter class

ITableDependencyFilter's Translate implementation must return a valid T-SQL preditace. It will be applyed to the WHERE SqlTableDependency trigger. In this example we want receive notification only when there is an Update/Delete or Insert with Id = 2:

public class CustomSqlTableDependencyFilter : ITableDependencyFilter
{
    private readonly int _id;

    public CustomSqlTableDependencyFilter(int id)
    {
        _id = id;
    }

    public string Translate()
    {
        return "[Id] = " + _id;
    }
}

static void Main(string[] args)
{
    var connectionString = ConfigurationManager
        .ConnectionStrings["SqlServerConnectionString"]
        .ConnectionString;

    ITableDependencyFilter whereCondition= new CustomSqlTableDependencyFilter(2);

    var mapper = new ModelToTableMapper<Product>();
    mapper.AddMapping(c => c.ItemsInStock, "Quantity");

    using (var dep = new SqlTableDependency<Product>(
        connectionString, 
        "Products", 
        mapper: mapper, 
        filter: whereCondition))
    {
        dep.OnChanged += Changed;
        dep.Start();

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

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

    Console.WriteLine(@"DML operation: " + e.ChangeType);
    Console.WriteLine(@"CustomerID:    " + changedEntity.Id);
    Console.WriteLine(@"CategoryId:    " + changedEntity.CategoryId);
    Console.WriteLine(@"Name:          " + changedEntity.Name);
    Console.WriteLine(@"Quantity:      " + changedEntity.ItemsInStock);
}


Last edited Mar 25 at 3:58 PM by delbianco, version 27