sql-dependency-notifier

A minimal C# console application that demonstrates real-time change detection in a SQL Server table using SqlDependency and SQL Server Service Broker — no polling, no timers.

How it works

SqlDependency is a .NET feature that hooks into SQL Server's Service Broker to receive a push notification whenever the result set of a registered query changes. The app:

1. Reads and prints the current table contents on startup
2. Registers a SqlDependency on the dbo.Messages table (filtered to Status = 'New')
3. When a row is inserted, updated, or deleted — the callback fires automatically
4. The callback prints the updated table contents and re-registers the listener

Prerequisites

• .NET 10 SDK
• SQL Server Express (or any SQL Server edition) with Service Broker enabled
• Windows Authentication access to the database

Database setup

Run the included script to create the database, enable Service Broker, create the table, and insert a sample row:

-- create-playground.sql

sqlcmd -S "$env:COMPUTERNAME\SQLEXPRESS" -i create-playground.sql

Or open create-playground.sql in SSMS and execute it.

Running the app

dotnet run

The connection string in Program.cs is configured for a local SQL Server Express instance and uses the current machine name as the server. This is a development convenience and will not work in most other environments.

Before running, update connStr in Program.cs to match your SQL Server setup:

Scenario	Server= value
Local SQL Server Express	{Environment.MachineName}\\SQLEXPRESS
Local default instance	localhost
Remote / named instance	your-server-name\\instancename
Azure SQL / production	Use a full ADO.NET connection string from your provider

For non-development environments, consider reading the connection string from an environment variable or a configuration file instead of hardcoding it.

Trigger a notification

While the app is running, insert or update a row in SSMS:

INSERT INTO dbo.Messages (Message, Status) VALUES ('Hello!', 'New');

The console will instantly print the updated table contents.

Project structure

Program.cs               — Application entry point and all logic
create-playground.sql    — SQL script to set up the database
sql-dependency-notifier.csproj

Key SqlDependency rules

For SqlDependency to work, the monitored query must follow these rules (otherwise the subscription silently fails with type Subscribe instead of Change):

• Use explicit column names — no SELECT *
• Use two-part table names — dbo.Messages, not just Messages
• No aggregates, subqueries, or TOP
• Service Broker must be enabled on the database

Article

This project is accompanied by a detailed write-up explaining how SqlDependency works, how Service Broker fits in, and what pitfalls to watch out for:

SqlDependency in .NET — Query Notifications and Real-Time Data Change Reactions

Tech stack

Runtime	.NET 10
SQL client	Microsoft.Data.SqlClient 7.0
Notification mechanism	SqlDependency / SQL Server Service Broker