Monitor data changes with SQL Server query notifications

Enable Service Broker and Set Permissions

Before you can use query notifications, you need to enable SQL Server’s Service Broker and configure the necessary permissions. Query notifications work by creating triggers for database changes and sending those changes to a message queue. Your code subscribes to this queue, and when new messages are enqueued, it receives push notifications to handle the changes.

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [MyDatabase] SET ENABLE_BROKER;
ALTER DATABASE [MyDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Configure Permissions

To create a user for a login and grant the necessary permissions for SqlTableDependency to function properly you can run the follow sql script.

USE [MyDatabase]

CREATE USER [appUser] FOR LOGIN [ServerName\Login]
WITH DEFAULT_SCHEMA=[dbo]

GRANT ALTER to [appUser]
GRANT CONNECT to [appUser]
GRANT CONTROL to [appUser]
GRANT CREATE CONTRACT to [appUser]
GRANT CREATE MESSAGE TYPE to [appUser]
GRANT CREATE PROCEDURE to [appUser]
GRANT CREATE QUEUE to [appUser]
GRANT CREATE SERVICE to [appUser]
GRANT EXECUTE to [appUser]
GRANT SELECT to [appUser]
GRANT SUBSCRIBE QUERY NOTIFICATIONS to [appUser]
GRANT VIEW DATABASE STATE to [appUser]
GRANT VIEW DEFINITION to [appUser]

If you don't set them properly, you may encounter errors like:

Unhandled Exception: TableDependency.SqlClient.Exceptions.UserWithNoPermissionException: User without permissions.
at TableDependency.SqlClient.SqlTableDependency`1.CheckIfUserHasPermissions()

Create a Table and Model

For demonstration purposes, let's create a table and a corresponding model class.

Table:

CREATE TABLE [dbo].[Notifications](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [int] NOT NULL,
    [CreatedAt] [datetime] NOT NULL,
    CONSTRAINT [PK_NotificationQueue] PRIMARY KEY CLUSTERED ([Id] ASC)
)

Model Class:

public class Notification
{
    public int Id { get; set; }
    public string Message { get; set; }
    public int UserId { get; set; }
    public DateTime CreatedAt { get; set; }
}

Add a Reference to SqlTableDependency

To work with SQL Server query notifications, you need a library that facilitates this. While SQL Server's built-in SqlDependency can be used, it's not as straightforward to work with. SqlTableDependency is a third-party library that offers a simpler integration.

Install SqlTableDependency:

Install-Package SqlTableDependency

You can install this via the Package Manager Console in Visual Studio (View > Other Windows > Package Manager Console).

Integrate SqlTableDependency and Handle Data Changes

Next, integrate SqlTableDependency into your application to listen for changes to the table.

The repository class will:

• Accept a connection string and table name.
• Listen for changes through the OnChanged event.
• Implement the IDisposable pattern to clean up resources when no longer needed.

For example:

using TableDependency.SqlClient;
using TableDependency.SqlClient.Base.EventArgs;

public class NotificationRepository : IDisposable
{
    private SqlTableDependency<Notification> sqlTableDependency;

    public void Start(string connectionString)
    {
        sqlTableDependency = new SqlTableDependency<Notification>(connectionString, "Notifications");
        sqlTableDependency.OnChanged += OnChanged;
        sqlTableDependency.Start();
    }

    public event EventHandler<Notification> NewNotificationReceived;
    private void OnChanged(object sender, RecordChangedEventArgs<Notification> e)
    {
        if (e.ChangeType == TableDependency.SqlClient.Base.Enums.ChangeType.Insert)
        {
            NewNotificationReceived?.Invoke(this, e.Entity);
        }
    }

    #region IDisposable Support
    private bool disposedValue = false;

    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            if (disposing && sqlTableDependency != null)
            {
                sqlTableDependency.Stop();
                sqlTableDependency.Dispose();
            }
            disposedValue = true;
        }
    }
    public void Dispose() => Dispose(true);
    #endregion
}

This implementation listens for changes to the Notifications table, specifically for newly inserted records. When a change is detected, the NewNotificationReceived event is triggered.

var notificationRepository = new NotificationRepository();
notificationRepository.NewNotificationReceived += (_, notification) => 
    Console.WriteLine($"New notification at {notification.CreatedAt}: {notification.Message}");

Task.Run(() =>
{
    notificationRepository.Start(connectionString);
});

To test if everything works, run your application and insert a test record into the database:

INSERT INTO [dbo].Notifications ([Message], [UserId], [CreatedAt])
VALUES ('New update available', 42, GETDATE())

Your application should receive the notification and output something like:

1/18/2025 3:15:45 PM    New update available