Event-driven .NET: How to use query notifications in SQL Server to monitor database changes

How do you check for new records in a database and react to the new data?

You really only have two options:

  1. Poll for changes every X seconds
  2. Use query notifications to be notified when new data is inserted

In this article I’ll show you how to configure query notifications so that your application receives push notifications from SQL.

There’s a built-in way to do this in .NET called SqlDependency. This works but it’s not well-designed – it’s a difficult API to use. There are multiple open source options that are easier to use. In this article I am using the open source library called SqlTableDependency.

1 – Add a reference to SqlTableDependency

For more details about how this works: https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

2 – Create a table and model

Messages table

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 ) )

Message model

public class Message { public int Id { get; set; } public string Text { get; set; } public string UserId { get; set; } public DateTime Timestamp { get; set; } }

3 – Enable Service Broker and configure permissions

The Service Broker is a feature in SQL Server that provides a message queue system. Query Notifications work by creating triggers on database changes and writing the changes to a message queue. Your external app subscribes to the message queue, and the Service Broker is responsible for sending new messages to your app.

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

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]

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:

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

Disclaimer: If you’re going to be setting this up in a production environment, you should work with the Database Admin to ensure your security and permissions are configured properly.

4 – Listen for table change events

Create a repository class that encapsulates using SqlTableDependency

This repository listens for query notifications from SqlTableDependency and fires the OnNewMessage event when data has been inserted.

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.

using MessageQueueService.Model; using System; using TableDependency.SqlClient; using TableDependency.SqlClient.Base.EventArgs; namespace MessageQueueService.Data { public delegate void NewMessageHandler(Message message); 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 NewMessageHandler OnNewMessage; private void HandleOnChanged(object sender, RecordChangedEventArgs<Message> e) { if(e.ChangeType == TableDependency.SqlClient.Base.Enums.ChangeType.Insert) { OnNewMessage?.Invoke(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 } }

Init the MessageRepository in a service and listen for new messages

This is initializing the MessageRepository and displaying the new messages that are being inserted.

using MessageQueueService.Data; using MessageQueueService.Model; using System; using System.Configuration; using System.ServiceProcess; using System.Threading.Tasks; namespace MessageQueueService { public partial class Service : ServiceBase { private readonly MessageRepository messageRepository; public Service() { InitializeComponent(); messageRepository = new MessageRepository(); messageRepository.OnNewMessage += MessageRepository_OnNewMessage; } private void MessageRepository_OnNewMessage(Message message) { Console.WriteLine($"{message.Timestamp}\t{message.Text}"); } protected override void OnStart(string[] args) { new System.Threading.Thread(StartService).Start(); } protected override void OnStop() { messageRepository.Dispose(); } internal void StartService() { Task.Run(() => messageRepository.Start(ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString)); } } }

Note: In the ConnectionString whatever security you use (integrated or specifying a user), make sure that login is linked to the user / permissions you created in Step 3 above.

5 – Run the service and insert a test record

Leave a Comment