How to Make SQL Server Notifications Work in ASP.NET Core

In this article, we’ll dive into setting up SQL Server notifications in ASP.NET Core using SqlDependency. SQL Server notifications are useful for scenarios where your application needs to respond to database changes, such as real-time updates for client applications. We’ll go over setting up SqlDependency, configuring Service Broker, handling common issues, and ensuring reliable notifications in production.

What are SQL Server Notifications?

SQL Server notifications allow an application to receive real-time alerts when data in a monitored table changes. For example, you might want a web app to update clients instantly when a record is added, updated, or deleted.

Why Use SqlDependency?

SqlDependency is a built-in .NET class that leverages SQL Server’s Service Broker feature to subscribe to notifications. With SqlDependency, we can monitor specific SQL queries and automatically notify our application of changes.

Before getting started, ensure you have:

• SQL Server 2005 or higher (with Service Broker enabled).
• ASP.NET Core 6.0 or higher.
• Basic knowledge of C# and SQL.

Setting Up SQL Server for Notifications

Step 1: Enable Service Broker

Service Broker must be enabled on the target database for SqlDependency to work. Run the following SQL command to enable it:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Note: Enabling Service Broker may interrupt other ongoing transactions, so use WITH ROLLBACK IMMEDIATE carefully, especially in production environments.

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourUser];
GRANT RECEIVE ON QUEUE::[dbo].[SqlQueryNotificationService-YourQueueName] TO [YourUser];
GRANT REFERENCES ON SERVICE::[dbo].[SqlQueryNotificationService] TO [YourUser];

Permissions are essential for setting up a reliable notification system. Without these, the notifications might fail silently.

Configuring SqlDependency in ASP.NET Core

1. Initialize SqlDependency in Program.cs:

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

SqlDependency.Start(connectionString);

app.Lifetime.ApplicationStopping.Register(() => SqlDependency.Stop(connectionString));
app.Run();

2. Create the SqlDependency Service: In your SqlDependencyService, set up SqlDependency to monitor changes to your table:

public class SqlDependencyService
{
    private readonly string _connectionString;
    private readonly IHubContext<NotificationHub> _hubContext;

    public SqlDependencyService(IConfiguration configuration, IHubContext<NotificationHub> hubContext)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
        _hubContext = hubContext;
        StartMonitoring();
    }

    public void StartMonitoring()
    {
        using (var conn = new SqlConnection(_connectionString))
        {
            var cmd = new SqlCommand("SELECT Name FROM dbo.Products", conn);
            var dependency = new SqlDependency(cmd);
            dependency.OnChange += OnDatabaseChange;

            conn.Open();
            cmd.ExecuteReader();
        }
    }

    private void OnDatabaseChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Update)
        {
            _hubContext.Clients.All.SendAsync("ReceiveUpdate", "Database updated!");
        }
        StartMonitoring(); // Resubscribe to changes
    }
}

3. Integrate SqlDependencyService with ASP.NET Core Dependency Injection:

builder.Services.AddSingleton<SqlDependencyService>();

Troubleshooting Common Issues

• Invalid Info in SqlNotificationEventArgs: Ensure the monitored table has no computed columns, as Service Broker rejects computed columns.
• No Subscription Found in sys.dm_qn_subscriptions: Verify Service Broker is enabled and that the SQL Server user has SUBSCRIBE QUERY NOTIFICATIONS permission.
• Error in sys.transmission_queue: Check for routing or permission errors if Service Broker messages fail to send.

For additional troubleshooting, use SQL Server Profiler to trace Broker:Conversation and Broker:Message Classify events, which can reveal issues in Service Broker conversations.

Best Practices

• Use Simple Queries: Keep monitored queries simple—no joins, aggregates, or subqueries.
• Specify Schema in Queries: Always use the full schema (dbo.Products) to avoid ambiguity.
• Monitor the sys.transmission_queue: Check for any errors that could prevent notifications from reaching your application.

Conclusion

With SqlDependency and Service Broker configured, your ASP.NET Core app can now receive real-time updates from SQL Server. By following these steps and best practices, you’ll have a reliable setup for SQL Server notifications that can power features like real-time data updates and client notifications.