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

Imagine your application constantly bombarding the database with questions: "Has anything changed? How about now? And now?" Every second, every minute. You're needlessly burning CPU, network, and database resources, even though the data might only change once an hour.

There's a much better way. Let the database tell you when something changes. That's exactly what SqlDependency is for.

SqlDependency is a relatively little-known technology, yet it's a great fit for many projects that work with SQL databases. In this article, we'll look at how it works in .NET, how to set it up step by step, and how to build a simple console application that reacts to data changes in real time.

What Is SqlDependency?

SqlDependency is a class in .NET that allows your application to receive notifications from SQL Server whenever the data you're querying changes. Under the hood, it relies on two key SQL Server mechanisms:

• Service Broker – an internal messaging system built directly into SQL Server
• Query Notification – a mechanism that monitors whether the result of a specific query has changed

The principle is simple: instead of repeatedly asking the database (polling), you register a "subscription" for a specific query. When the result changes (INSERT, UPDATE, DELETE), SQL Server sends a notification and your application reacts to it.

Polling vs. SqlDependency

With traditional polling, your application queries the database every X seconds — even when nothing has changed. This wastes CPU, network, and database resources, and the change is only detected on the next cycle.

SqlDependency works the other way around. The database itself notifies the application when a change occurs. Queries are only executed when truly needed, and the reaction is nearly instant. Instead of a pull model, you switch to push.

Put simply: polling is like constantly opening the fridge to check if new food appeared. SqlDependency is like a doorbell that rings when the delivery arrives.

When Not to Use SqlDependency?

• When you need historical data about what changed (SqlDependency only tells you that a change occurred)
• With very complex queries — there are syntax restrictions
• When periodic reads are sufficient and you don't need real-time reactions
• In Azure SQL, where Service Broker support is limited

How It Works Under the Hood

The entire process happens in several steps:

1. The application calls SqlDependency.Start() — an internal listener is created
2. You create a SqlCommand and attach a SqlDependency to it
3. You call ExecuteNonQuery() or ExecuteReader()
4. Data changes (INSERT, UPDATE, DELETE)
5. SQL Server sends a notification
6. The registration is removed — it's a one-time subscription

What We're Going to Build

Let's move from theory to practice. We'll create a simple console application in .NET 10 that monitors a Messages table in a SQL Server database.

The table will serve as a simple message queue. Each record contains a message text and a status (New, Processed). At startup, the application registers to watch for messages with the status 'New'. When a change occurs, it immediately receives a notification, prints the current state of the table, and re-registers for further changes.

The entire solution consists of a single SQL script and a single Program.cs file. No complex architecture — just a clean demonstration of SqlDependency in action.

Setting Up the Database (Step by Step)

Before writing any C# code, we need to prepare the database and the environment we'll be testing with.

Creating the Database

USE master;
GO
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'SimpleSqlDepDB')
    CREATE DATABASE SimpleSqlDepDB;
GO

Enabling Service Broker

USE SimpleSqlDepDB;
GO
ALTER DATABASE SimpleSqlDepDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

Important: SqlDependency will not work without Service Broker enabled. This is the most common mistake. Service Broker can also be disabled after a SQL Server restart, so always verify its status.

Creating the Table

IF OBJECT_ID('dbo.Messages', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Messages (
        Id INT IDENTITY(1,1) PRIMARY KEY,
        Message NVARCHAR(100) NOT NULL,
        Status NVARCHAR(20) NOT NULL DEFAULT 'New'
    );
END
GO

Inserting Test Data

INSERT INTO Messages (Message, Status) VALUES ('Initial message', 'New');
GO

Table and Query Requirements

SqlDependency has strict rules about what kind of queries you can use. It's worth considering these constraints early on to make sure they fit your architecture.

You must follow these rules:

• The table must have a primary key or a unique index
• All columns must be explicitly listed (no SELECT *)
• Use two-part table names (dbo.Messages)

The query must not contain:

• SELECT *
• SELECT DISTINCT
• UNION
• Certain subqueries
• TOP without ORDER BY
• Aggregate functions (COUNT, SUM, AVG)

C# Implementation (.NET 10)

Now let's look at the actual implementation. The project uses .NET 10 and the Microsoft.Data.SqlClient package.

Project Structure

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="7.0.0" />
  </ItemGroup>
</Project>

Complete Code (Program.cs)

using System;
using System.Data;
using Microsoft.Data.SqlClient;

class Program
{
    // Connection string – connecting to a local SQL Server Express instance
    static readonly string connStr = $"Server={Environment.MachineName}\\SQLEXPRESS;Database=SimpleSqlDepDB;Trusted_Connection=True;TrustServerCertificate=True;";

    // The query we'll be monitoring
    // NOTE: Must use explicit column names and a two-part table name (dbo.Messages)
    static readonly string query = "SELECT Id, Message, Status FROM dbo.Messages WHERE Status = 'New'";

    static void Main(string[] args)
    {
        Console.WriteLine("Listener started. Waiting for a value change...");

        // 1. INITIALIZATION – Start the internal infrastructure for receiving notifications
        SqlDependency.Start(connStr);

        // Print the current state of the table
        Console.WriteLine("\n--- Initial table data ---");
        ReadTableData();

        // 2. REGISTRATION – Subscribe to data change notifications
        StartListening();

        Console.WriteLine("\nPress Enter to exit...");
        Console.ReadLine();

        // 3. CLEANUP – Shut down gracefully and release resources
        SqlDependency.Stop(connStr);
    }

    static void ReadTableData()
    {
        using SqlConnection conn = new SqlConnection(connStr);
        conn.Open();

        using SqlCommand cmd = new SqlCommand(query, conn);
        using SqlDataReader reader = cmd.ExecuteReader();

        if (!reader.HasRows)
        {
            Console.WriteLine("(no rows)");
            return;
        }

        // Formatted table output to the console
        Console.WriteLine($"{"Id",-5} {"Message",-30} {"Status",-15}");
        Console.WriteLine(new string('-', 52));

        while (reader.Read())
        {
            Console.WriteLine($"{reader["Id"],-5} {reader["Message"],-30} {reader["Status"],-15}");
        }
    }

    static void StartListening()
    {
        using SqlConnection conn = new SqlConnection(connStr);
        conn.Open();

        using SqlCommand cmd = new SqlCommand(query, conn);

        // Create the dependency and attach the event handler
        SqlDependency dependency = new SqlDependency(cmd);
        dependency.OnChange += Dependency_OnChange;

        // ExecuteNonQuery registers the query on the SQL Server side
        cmd.ExecuteNonQuery();
    }

    static void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        Console.WriteLine($"\n⚡ Notification — Type: {e.Type}, Info: {e.Info}, Source: {e.Source}");

        if (e.Type == SqlNotificationType.Change)
        {
            try
            {
                Console.WriteLine("\n--- Table data after change ---");
                ReadTableData();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error reading table data: {ex.Message}");
            }
        }
        else
        {
            // If the type is not Change, it indicates an error or an invalid query
            Console.WriteLine("Subscription error or invalid query — not re-subscribing.");
            return;
        }

        // IMPORTANT: After receiving a notification, we MUST re-register!
        try
        {
            StartListening();
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error re-registering listener: {ex.Message}");
        }
    }
}

Code Walkthrough

Let's go through the key parts.

Connection String

We're connecting to a local SQL Server Express instance using Windows authentication. Adjust the connection string to match your own environment.

SqlDependency.Start() and Stop()

Start() initializes the infrastructure for receiving notifications, and Stop() shuts it down cleanly. Both calls are important to prevent resource leaks.

Registering the Dependency

By creating a SqlDependency and attaching it to a SqlCommand, you register the query for monitoring on the SQL Server side. The actual registration happens when the command is executed.

Re-registration

This is a key detail. The registration is always one-time only. After a notification is received, it's automatically removed and must be recreated.

SqlNotificationEventArgs

This object contains information about the notification: type, detail, and source. A value of Change means the data actually changed; other values usually indicate a problem.

Testing

After running the application (dotnet run), you'll see the current state of the table and the application will begin waiting for changes:

Listener started. Waiting for a value change...

--- Initial table data ---
Id    Message                        Status
----------------------------------------------------
1     Initial message                New

Press Enter to exit...

If you insert a new record or modify an existing one, the notification arrives immediately and the application prints the updated data. The change is only detected if it affects the result of the monitored query.

Open SQL Server Management Studio or use any other tool to send an SQL query and make a change:

-- Insert a new record
INSERT INTO dbo.Messages (Message, Status) VALUES ('Test notification', 'New');

You'll immediately see in the console:

⚡ Notification — Type: Change, Info: Insert, Source: Data

--- Table data after change ---
Id    Message                        Status
----------------------------------------------------
1     Initial message                New
2     Test notification              New

Try an UPDATE as well:

UPDATE dbo.Messages SET Status = 'Processed' WHERE Id = 1;

⚡ Notification — Type: Change, Info: Update, Source: Data

--- Table data after change ---
Id    Message                        Status
----------------------------------------------------
2     Test notification              New

Notice that after the UPDATE, the record with Id = 1 no longer appears, because our query only filters for records with Status = 'New'.

Common Pitfalls

• Service Broker is not enabled — The most common issue. Verify its status and enable it if needed.

• Incorrect query format — SqlDependency is strict about syntax. If the query doesn't meet the requirements, registration will fail.

• Forgetting to re-register — Without re-subscribing, you'll only receive a single notification.

• Connection leaks — Use using blocks. Even when the connection is closed, the registration on the SQL Server side remains active.

Real-World Use Cases

SqlDependency is a good fit wherever you need to react to data changes immediately.

A typical example is live dashboards that only refresh when data actually changes, or simple job queues where new records trigger processing. It's also commonly used for cache invalidation — the application receives a notification and knows it's time to refresh cached data.

It also has a place in IoT scenarios and simple real-time notifications where fast reactions without unnecessary polling are essential.

Comparison with Alternatives

• SqlDependency — a simple solution built into SQL Server, but with limitations
• Polling — universal but inefficient
• SignalR — great for real-time web, requires an additional layer
• Message Queue — a robust solution for distributed systems
• Change Tracking / CDC — suited for auditing and change history

Summary

SqlDependency is an elegant way to react to database changes without unnecessary polling. Instead of constantly querying, let SQL Server alert you only when something actually changes.

Fewer queries, less load, and faster reactions. Exactly what we expect from modern applications.

The complete source code for this project is available on GitHub: github.com/mortylen/sql-dependency-notifier