Asp.Net MVC and SignalR

Question

Hello everyone and thanks for the help in advance. I am refactoring older SignalR code to work with the latest versions of Asp.Net. The application attempts to use Sql Dependency notifications to send updates to the client, however, there appears to be very few examples of this. My hub looks like this:

    public class CallNotificationHub : Hub
    {
        private SqlDependency dependency;
        public override Task OnConnectedAsync()
        {

            CallNotificationHub nHub = new CallNotificationHub();
            nHub.SendCallNotifications();

            return base.OnConnectedAsync();
        }

        public async Task SendCallNotifications()
        {
            List<InboundTwilioCalls> InboundCalls = new List<InboundTwilioCalls>();
            
            try
            {
                string connectionString = "";
                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();
                dt.Load(reader);
                List<InboundTwilioCalls> inboundTwilioCalls = new List<InboundTwilioCalls>();
                using (var ctx = new KidslocalContext())
                {
                    inboundTwilioCalls = ctx.InboundTwilioCalls.FromSqlRaw("Exec sp_GetCalls").ToList();

                }
            }
            catch (Exception ex2)
            {

            }
            string message = "Changed";
            await Clients.All.SendAsync("ReceiveMessage", message);
        }

        private async void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                CallNotificationHub nHub = new CallNotificationHub();
                await nHub.SendCallNotifications();
            }
        }
    }

However, when the database updates, I receive a console message:

GET http://192.168.0.3:19880/callNotificationHub?id=T2z2JBaa8ifVlNniyM-75A net::ERR_CONNECTION_RESET 200 (OK)

I am really not sure what to do with this. I did find one article located at https://learn.microsoft.com/en-us/answers/questions/1343607/asp-net-core-7-0-sql-dependency-onchange-doesnt-fi which uses the repository pattern rather than the typical SignalR example, but it would seem I would not need to use this pattern to make the application work. Any help would be appreciated.

Answer 1

Sorry the following articles are written in Japanese. But they include sample code of ASP.NET MVC application that uses SignalR to provide server broadcast triggered by the notification from the SqlDependency. Hope that the code is readable and helpful for you.

SignalR and SqlDependency (.NET Framework 4.8) [http://surferonwww.info/BlogEngine/post/2021/12/26/signalr-and-sqldependency-on-aspnet-web-application.aspx]

ASP.NET Core and SqlDependency (.NET 6.0) [http://surferonwww.info/BlogEngine/post/2022/01/01/use-of-sqldependency-in-aspnet-core-application.aspx]