C# – Monitor data changes with SQL Server query notifications

by

You can use SQL Server query notifications to send push notifications to your code when data changes. This is an alternative to polling the database for changes. In this article, I’ll show how to configure this feature and work with it in the code.

1 – Enable Service Broker and configure permissions

You need to enable the SQL Server Service Broker feature and configure permissions to be able to use query notifications. Query notifications work by creating triggers on database changes and writing the changes to a message queue. Your code subscribes to the message queue. When the Service Broker enqueues new messages, your code is sent a push notification and you can react to the change.

Enable Service Broker

ALTER DATABASE [MessageDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [MessageDatabase] SET ENABLE_BROKER; 
ALTER DATABASE [MessageDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE
Code language: SQL (Structured Query Language) (sql)

Configure permissions

This SQL script will create a user for a login on the proper database, and set all the permissions required for SqlTableDependency to work. 

USE [DATABASENAME]

CREATE USER [makolyte] FOR LOGIN [PCName\LoginName]
WITH DEFAULT_SCHEMA=[dbo]


GRANT ALTER to [makolyte]
GRANT CONNECT to [makolyte]
GRANT CONTROL to [makolyte]
GRANT CREATE CONTRACT to [makolyte]
GRANT CREATE MESSAGE TYPE to [makolyte]
GRANT CREATE PROCEDURE to [makolyte]
GRANT CREATE QUEUE to [makolyte]
GRANT CREATE SERVICE to [makolyte]
GRANT EXECUTE to [makolyte]
GRANT SELECT to [makolyte]
GRANT SUBSCRIBE QUERY NOTIFICATIONS to [makolyte]
GRANT VIEW DATABASE STATE to [makolyte]
GRANT VIEW DEFINITION to [makolyte]
Code language: SQL (Structured Query Language) (sql)

Note: Be sure to review the security configuration with your Database and Network Admins.

Getting the permissions right is one of the most difficult parts of getting this to work. If you don’t do it right, you’ll run into this error in the code.

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

2 – Create a table and model

To show query notifications working, I’ll create a table and model.

Here’s the table definition:

CREATE TABLE [dbo].[Messages](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Text] [nvarchar](280) NOT NULL,
	[UserId] [int] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_MessageQueue] PRIMARY KEY CLUSTERED 
 ( [Id] ASC )
)
Code language: SQL (Structured Query Language) (sql)

Here’s the model class:

public class Message
{
	public int Id { get; set; }
	public string Text { get; set; }
	public string UserId { get; set; }
	public DateTime Timestamp { get; set; }
}
Code language: C# (cs)

3 – Add a reference to SqlTableDependency

To work with SQL Server query notifications in the code, use a library that integrates with it, such as:

  • SqlDependency. This is a built-in library, but it’s difficult to work with since it’s no well-designed.
  • SqlTableDependency. This is a third-party library that’s well-designed.

I suggest using SqlTableDependency. To use this, first add the SqlTableDependency package:

Install-Package SqlTableDependency
Code language: PowerShell (powershell)

Note: This is using Package Manager Console in VS (View > Other Windows > Package Manager Console).

4 – Integrate with SqlTableDependency and handle data changes

Integrate with SqlTableDependency in a repository class.

  • Pass in the connection string.
  • Pass in the name of the table you want to monitor for changes.
  • Handle the OnChanged event.
  • Implement the dispose pattern.

In this example, it’s passing in the name of the Messages table. In the OnChanged event handler, it’s specifically checking for newly inserted records. Then it’s firing its own event (HandleOnChanged).

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

public class MessageRepository : IDisposable
{
    private SqlTableDependency<Message> sqlTableDependency;

    public void Start(string connectionString)
    {
        sqlTableDependency = new SqlTableDependency<Message>(connectionString, "Messages");
        sqlTableDependency.OnChanged += HandleOnChanged;
        sqlTableDependency.Start();
    }
    public event EventHandler<Message> OnNewMessage;
    private void HandleOnChanged(object sender, RecordChangedEventArgs<Message> e)
    {
        if (e.ChangeType == TableDependency.SqlClient.Base.Enums.ChangeType.Insert)
        {
            OnNewMessage?.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
}
Code language: C# (cs)

Note: This implements IDisposable so that it can dispose of SqlTableDependency. This is important, because it tears down the Service Broker resources it spun up when it was started.

The repository directly integrates with SqlTableDependency and encapsulates it. It fires an event when relevant data changes happen. Here’s an example of listening to this event from the repository and simply writing it to the console:

var messageRepository = new MessageRepository();
messageRepository.OnNewMessage += (_, msg) => Console.WriteLine($"{msg.Timestamp}\t{msg.Text}");

Task.Run(() =>
{
    messageRepository.Start(connectionString);
});
Code language: C# (cs)

5 – Run the code and insert a test record

To see query notifications working, run the code, and then insert a record into the table:

INSERT INTO [dbo].Messages ([Text], [UserId], [Timestamp])
VALUES ('hello world', 1, GETDATE())
Code language: SQL (Structured Query Language) (sql)

The code receives a notification about the insertion and outputs the following:

10/24/2022 6:24:21 AM	hello worldCode language: plaintext (plaintext)

Related Articles