Display Live Data From Database in Blazor (SqlTableDependency & SignalR)

Displaying live data from a database on your website can be a very powerful feature for reports and dashboards. To do it in a Blazor Server App is actually not that complicated as you might think. You need to understand two concepts. SqlTableDependency and SignalR.

What is SqlTableDependency?

SqlTableDependency is a library/class that can be used to track changes in a MS SQL database. An important step in the database is to enable the Service Broker. You can do it with this script:

After enabling the Service Broker you can start listen to the tables in the database.

With SqlTableDependency it is possible to also track what type of change that is made, however in this setup we just listen for if any change is made. When you make a new connection to the database with SqlTableDependency you also specify a method that is going to be runned every time there is a change in the database. By triggering that method, we can now use SignalR to actually update the website/app live without any refreshing of the page.

What is SignalR?

SignalR is used to create a central hub that users can connect to. This central hub can be used to push data to the users view without refreshing the page that the user is on. In fact it is not really a logged in user, it should just be referenced as a client. When a page on the website is loaded, we tell in the code that the client should connect to the hub. In this project we tell the client to connect to the EmployeeHub, and then when the listener method in SqlTableDependency is called, we can send an updated list to the EmployeeHub that the user is connected to.

Setting up the project

First you need to enable the Service Broker in your database as descriped earlier.

Then we can go and install all the nuget packages for the project. Remember this project is created in .NET 6, so you need to choose the nuget packages that match the .NET version. Except the SqlTableDependency nuget package.

• Microsoft.AspNetCore.SignalR.Client (6.x.xx)
• Microsoft.EntityFrameworkCore (6.x.xx)
• Microsoft.EntityFrameworkCore.SqlServer (6.x.xx)
• Microsoft.EntityFrameworkCore.Tools (6.x.xx)
• SqlTableDependency (Just the newest version)

Program.cs

Index.razor

EmployeeHub.cs

EmployeeService.cs

AppDbContext.cs

Employee.cs