Getting up-to-date data from SQL into your browser window is usually an active process. Let's say you have a simple web application that retreives data from a database. You tell your browser to fetch a page, it contacts the web server with your request, which pulls the data from a database, and then the whole process reverses all the way back up the chain until you get a webpage in your browser. What you now have is a snapshot of the state that database was in when you made that request. If someone else alters that data in the database, the only way you find about it is to repeat the process by refreshing your browser.
But what if we wanted to take that active process and flip it on its head, making it a passive one instead? Instead of the user querying for new updates, the web server would simply push them to the browser as they occur. If another user makes an update to the dataset and it appears on all user's screens just moments later. With SignalR and SQL Server Service Broker, we can do just that.
The first step of the process is to set up a service broker in our database which will watch for changes and can update any listeners of them. The setup of this service broker is non-trivial, but fortunately, better developers than I have done the leg work. I utilized the NuGet package SqlServiceBrokerListener, the source of which is available on github. I'm going to cover the high-level ideas, but if you're interested in the inner workings and exact syntax, I encourage you to check it out.
The service broker writes the messages passed to it to a corresponding SQL queue. Anyone can read from the queue, but if we want to make modifications to its contents, all such transactions must be performed by the service broker.
One of the things we might want to do with that event is to pass that data change on to any connected users, so we can update their browser's copy of the data. This sort of server-initiated push usually involves creating a websocket connection on the initial page load, and then streaming data to the connected user.
With a Blazor Server-Side application, this websocket-based communication is fundamental to how the application works. The user creates a signalR connection on page load, and then changes to the page they are viewing are streamed to them via that connection. In a standard application, the initial page load will follow the pattern of:
This allows us to inject the service into any Blazor component we have. Here I've got a simple component that uses a data service to fetch the original table objects, then listens for updates and updates the component's view when a table update is received.
Therefore, I instead find this solution using SHA256 to hash together the various pieces of your identity to be preferable:
I'm using SHA256, but any hashing algorithm will probably be sufficient here, as we are unlikely to ever have enough application listeners to cause a hash collision.
This application structure is powerful, but in most cases users of a website don't require such real-time updates, and can be served just as well by the simpler user-initiated page refresh model. If you do choose to leverage it, I would only caution against having too many listener/triggers operating at once, as this can place unnecessary computing strain on your database.
A final important caveat: Service brokers are not supported in Azure SQL Database. If you want to use this in an Azure environment, you can use a Managed Instance instead. See a comparison of the two types of Azure SQL offerings here.