Problems with SignalR Core and SQL Notification

Question

Hello everyone and thanks for the help in advance. I am developing a SignalR .Net 5 MVC application that uses SignalR to notify clients of inbound calls logged to a database. I am using the javascript sdk to perfrom client notifications. I ahve also confirmed the SQL Server Service Broker is enabled and working. Here is my controller code:

    public class CallNotificationHub : Hub
{
    public override async Task OnConnectedAsync()
    {

        await base.OnConnectedAsync();
    }

    public async Task SendCallNotifications()
    {
        
        try
        {
            string connectionString = myConnString;
            SqlDependency.Start(connectionString);
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();

            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT [ID], [TimeEntered], [CallFrom], [CallTo] FROM [dbo].[tbl_Log_InboundTwilioCalls]";
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            
            dependency = new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

            DataTable dt = new DataTable();

            dependency.AddCommandDependency(command);

            var reader = command.ExecuteReader();

            SendEmail sendEmail = new SendEmail("SendCallNotifications Reached", "New SignalR5 SendCallNotifications at " + DateTime.Now.ToString(), "");

        }
        catch (Exception ex)
        {
            SendEmail sendErrEmail = new SendEmail("SendCallNotifications Error", ex.ToString(), "");
        }

        await Clients.All.SendAsync("RecieveNotification", "Changed at " + DateTime.Now.ToString());
        
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        SendEmail sendEmail = new SendEmail("Dependency Change", e.Type.ToString() + " at " + DateTime.Now.ToString(), "");
        CallNotificationHub nHub = new CallNotificationHub();
        nHub.SendCallNotifications();

    }
}

The javascript notification script:

"use strict";

var connection = new signalR.HubConnectionBuilder().withUrl("/CallNotificationHub").build();

connection.on("RecieveNotification", (result) => {


console.log(result);
alert("Result=" + result);
alert("CallNotification2 Changed");
});

connection.start().then(function () {
    connection.invoke("SendCallNotifications").catch(err => console.error(err));
}).catch(function (err) {
return console.error(err.toString());
});

On the initial page load, the notification works as expected and writes a response to the console. However, when the database updates, the email notifications fire, however, the clients do not update. I stuck as to where to go from here. Any help would be appreciated.

I have seen you have created a same issue in 5/25/2021, and why you create the sample post. The problem persists or the problem is reproduced after modifying something. Please pay attention to whether the project has been updated and deployed recently. — buymeacoffee
– buymeacoffee, Commented Dec 15, 2021 at 6:02
There is no error message in output that I can see. I was never able to get ths project to work with .Net Core. Instead, I reverted back to .Net 4.5. — Greybeard
– Greybeard, Commented Dec 15, 2021 at 11:39
Pls enable tracing feature and use debugdiag tool to collect the logs. You need host your app in IIS first, and try to reproduce the issue, you can get the log. — buymeacoffee
– buymeacoffee, Commented Dec 16, 2021 at 3:20
I'll try that, but I really don't think it is an IIS issue. Within the same project, I have a working SignalR chat application. — Greybeard
– Greybeard, Commented Dec 16, 2021 at 22:55

buymeacoffee · Accepted Answer · 2021-12-22 07:15:14Z

0

Try to modify your code like below. It works for me.

using Microsoft.AspNetCore.SignalR;
using signalr.Interface;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace signalr.Hubs
{
    public class CallNotificationHub : Hub, ICallNotificationHub
    {
        public override async Task OnConnectedAsync()
        {

            await base.OnConnectedAsync();
        }

        public async Task SendCallNotifications()
        {

            try
            {
                string connectionString = @"Data Source=.************ue; Connection Timeout=30;";
                SqlDependency.Start(connectionString);
                SqlConnection connection = new SqlConnection(connectionString);
                connection.Open();

                SqlCommand command = new SqlCommand();
                command.CommandText = "SELECT *FROM [dbo].[Restaurant]";
                command.Connection = connection;
                command.CommandType = CommandType.Text;

                var dependency = new SqlDependency(command);
                counter = 0; //Whenewer the web method is called, set te counter to 0
                //dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                DataTable dt = new DataTable();

                dependency.AddCommandDependency(command);

                var reader = command.ExecuteReader();

                Console.WriteLine("SendCallNotifications Reached"+ "New SignalR5 SendCallNotifications at " + DateTime.Now.ToString());
                //SendEmail sendEmail = new SendEmail("SendCallNotifications Reached", "New SignalR5 SendCallNotifications at " + DateTime.Now.ToString(), "");

            }
            catch (Exception ex)
            {
                Console.WriteLine("SendCallNotifications Error " + DateTime.Now.ToString()+ ex.ToString());
                //SendEmail sendErrEmail = new SendEmail("SendCallNotifications Error", ex.ToString(), "");
            }

            await Clients.All.SendAsync("RecieveNotification", "Changed at " + DateTime.Now.ToString());
            Console.WriteLine("RecieveNotification " + "Changed at " + DateTime.Now.ToString());

        }
        public int counter = 0;
        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
       

            if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Update && counter == 0)
            {
                //Call SignalR  
                Console.WriteLine("Dependency Change " + e.Type.ToString() + DateTime.Now.ToString());
                //SendEmail sendEmail = new SendEmail("Dependency Change", e.Type.ToString() + " at " + DateTime.Now.ToString(), "");
                CallNotificationHub nHub = new CallNotificationHub();
                nHub.SendCallNotifications();
                counter++; //The update is done once
            }
            else
            {
                counter = 0; //if the update is needed in the same iteration, please don't update and set the counter to 0
            }

        }
    }
}

answered Dec 22, 2021 at 7:15

buymeacoffee

22.6k2 gold badges23 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Greybeard Over a year ago

Which leads me back to my original post. I think my problem is in the javascript update code, but am not sure. Are you receiving actual updates to the browser, or simply getting notifications from the controller.

buymeacoffee Over a year ago

@Greybeard Just try it.

Greybeard Over a year ago

I did. Tried it on laptop using VS 2019, SQL Server 2012, and IIS Express. Client only updates on first page load, but controller sends notifications Then moved to a production machine, Windows Server 2012, SQL Server 2012. Same result as the testing machine. Alerts fire 0n first page load. After first pabe load, only email notifications.

Greybeard Over a year ago

Does anyone have a truly working SignalR Core with SQL backplane? I'm obviosuly close to having this working, but don't find any examples. Is this no longer being supported?

buymeacoffee Over a year ago

@Greybeard can you schedule a time,and I can chat with you.

|

Collectives™ on Stack Overflow

Problems with SignalR Core and SQL Notification

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related