Route To Take With SqlDependency OnChange

Question

I am trying to integrate live updates in my ASP.NET WebAPI and I have hit a wall so to say. I have my application set up (using Angular on front end), and I've got it so when the page initially loads it issues a get request from my SQL database and the web page loads with this data. This data changes pretty frequently, and I want to incorporate live updates. I have my back end set up to use a SqlDependency to notify me when a change in the database occurs, but where to go from here I have no clue. I have tried to get SignalR to work with my OnChange event handler for SqlDependency but I didn't see too much online about this. I then thought it might be easier to use Angular and try polling, but again I did not see how I would incorporate this with my OnChange event handler. Any ideas on the simplest way I can communicate between the server and clients that the database has changed?

Studio

Answer 1

You could use a websocket technology like SignalR. There are three flavors for it: ASP.NET SignalR, ASP.NET Core SignalR and Azure SignalR (SignalR having Azure as backbone). Compare each edition here and here.

You should get a context from IHubContext and have it wired up as you can see here:

public class HomeController : Controller
{
    private readonly IHubContext<NotificationHub> _hubContext;

    public HomeController(IHubContext<NotificationHub> hubContext)
    {
        _hubContext = hubContext;
    }
}

According with the documentation, the IHubContext maybe used when:

The IHubContext is for sending notifications to clients, it is not used to call methods on the Hub.

Now, to wire up the client side, you may choose the right technology (javascript,typescript, etc). A sample (from microsoft docs) may be found here:

"use strict";

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

//Disable send button until connection is established
document.getElementById("sendButton").disabled = true;

connection.on("ReceiveMessage", function (user, message) {
    var li = document.createElement("li");
    document.getElementById("messagesList").appendChild(li);
    // We can assign user-supplied strings to an element's textContent because it
    // is not interpreted as markup. If you're assigning in any other way, you 
    // should be aware of possible script injection concerns.
    li.textContent = `${user} says ${message}`;
});

connection.start().then(function () {
    document.getElementById("sendButton").disabled = false;
}).catch(function (err) {
    return console.error(err.toString());
});

document.getElementById("sendButton").addEventListener("click", function (event) {
    var user = document.getElementById("userInput").value;
    var message = document.getElementById("messageInput").value;
    connection.invoke("SendMessage", user, message).catch(function (err) {
        return console.error(err.toString());
    });
    event.preventDefault();
});

One may also mention Socket.IO, which I personally never used before.

Happy coding

Recipe