ASP.NET Core 7.0 Sql Dependency OnChange doesn't fire

Question

Hey!

I am trying to implement real time notifications functionality and I am using SignalR and using Microsoft.Data.SqlClient;

I have a table created and appropriate SignalR Hub and controller functions. In my NotificationController, I do the following (apologies for not writing in a codeblock. Doing so happens to not render anything for some reason):

I call the above NotificationsDependency() in the ConfigureServices function before app.Run() gets called.

When I test it by inserting a value from MSSQL via query, it records the query being added but the OnDependencyChange function doesn't get triggered.

I can confirm that the enable_broker services is enabled for the database.

I've been stuck on this for over a week and would really appreciate some help on this!

Thank you!

Best,

Aakash

EDIT: I solved the issue. Apparently I had to make the database owner to someone else and it seems to work now and trigger the onChange function. Thank you all for your help!

Answer 1

you missed the call to SqlDependency.Start()

try:

public void NotificationsDependency()
{
  var connectionString = @"ConnectionString";
  SqlDependency.Start(connectionString, "QueueName");
  using (var connection = new SqlConnection(connectionString))
  {
    connection.Open();
    var sql = @"SELECT [Id], [TenantId], [MessageContent], [Datecreated], [ISRead], [ISArchived] FROM [dbo]- [UserNotifications]";
    using (var cmd = new SqlCommand(sql, connection))
    {
      var sqlDep = new SqlDependency(cmd);
      sqlDep.OnChange += OnDependencychange;
      cmd.ExecuteReader();
    }
  }
}

really should not be called from a controller, as you can get more than one running. try a background thread instead.

Answer 2

The following article includes the code to implement the SqlDependency in .NET 6.0 ASP.NET Core MVC application. Sorry it is written in Japanese but I hope that the code is readable.

http://surferonwww.info/BlogEngine/post/2022/01/01/use-of-sqldependency-in-aspnet-core-application.aspx

You will be able to use the translation service such as Google. Below is the results of translation for the beginning of the article:

"It seems that SqlDependency can be used even in .NET 6.0 apps by installing System.Data.SqlClient with NuGet (confirmed with the console app). Now that I know that, in my ASP.NET Core web app, I use the SqlDependency class to receive query notifications when data in SQL Server is updated, and when I receive the notification, I get the updated data from SQL Server. So, I made a sample that uses ASP.NET Core SignalR to deliver in real time to all connected clients."

Answer 3

@Aakash I want to provide you with some troubleshooting steps.

Here are some troubleshooting steps you can follow to identify why the OnDependencyChange function is not getting triggered:

Verify that the SqlDependency is properly started by calling the SqlDependency.Start() method in the ConfigureServices method of the Startup class. Ensure that the connection string is correct and the enable_broker option is enabled for the database.

Confirm that the SqlDependency is properly created and listening for changes by adding some logging statements in the OnConnectedAsync method of your SignalR Hub class. You can log the SqlDependency status and any errors that occur.

Ensure that the query used by the SqlDependency is correct and returns the expected results. You can test the query in SQL Server Management Studio or a similar tool.

Verify that the OnDependencyChange function is properly registered with the SqlDependency by adding some logging statements in the OnConnectedAsync method of your SignalR Hub class. You can log the registration status and any errors that occur.

Check that the OnDependencyChange function is properly implemented and handles the change event correctly. You can add some logging statements in the OnDependencyChange function to see if it is being called and if it is processing the change event correctly.

Ensure that the SqlDependency is properly stopped by calling the SqlDependency.Stop() method in the OnDisconnectedAsync method of your SignalR Hub class.

Check for any exceptions or errors being thrown by the SqlDependency or SignalR. You can check the application logs or use a debugger to identify any exceptions or errors.