Live data updating in Blazor

 

Providing live data updates or data synchronization in a multi-user environment has always been a bit challenging.  I have been chasing how to do live updates and day sync between desktop applications, web applications, and even temporarily disconnected clients.

I have worked with Sales applications and legal applications where users needed to work but did not have access to the internet or a network.  Even though I was able to provide a solution, they were too complex and too hard to maintain for what I care for.

Thanks to ZetBit, I was able to find an awesome solution to move forward with. This solution is only using Microsoft SQL Server and a cool Nuget package called SQLTableDependancy.  

For this solution, you need to enable the Service Broker in SQL Server.  This will generate events when records in the database are added, edited, or deleted.  Using the SQLTableDependancy class you set event listeners for what tables you want to listen to.

Thanks to the Blazor being designed to update UI changes, in your event handler, you refresh the collection of the table change and call "StateChanged".  That's pretty much it. Let's look into the details.

To see how this solution works simply run the application and go to the jobs page.  Open the database and open the Jobs table for edit.  Make a change in the database and watch it update automatically in the UI.  It's magic! 

Sample Application

For the sample application:

1. Created Balzor Server Project.  Would work the same for a WASM project
2. Added the Entity framework Nugets and the SQLTableDepednance Nuget
3. I used Entity Framework for my DAL.  I created a single class, Job, that I would be updating.
4. I created a Job Service class that would actually get the data.
5. I created a new page to display the jobs from the database.  I used the same process the Weather Fetch page uses. 
6. I tested to make sure the basic page was working.
7. I then  Wired in the Event Handler

SQL Server Database Create Enable Broker 

When you create the database to use with this project, you need to enable the broker service.  This is under "Other Options" => "Service Broker".  Then just create the new database.

Set up Event Listener

I created the event handler in the Job Service class.  You need a reference to the SQLTableDepenancy class:

private readonly SqlTableDependency<Job> _dependency;

In the constructor, you initialize the objects and define the event:

            _connectionString = "Server=ServerName;Database=CompanyDatabase;Trusted_Connection=True;";
            _dependency = new SqlTableDependency<Job>(_connectionString, "Jobs");
            _dependency.OnChanged += Changed;
            _dependency.Start();

 Note the connection string is required.  You would normally handle the security of the connection string as it is done in other places.  This is just to show what is need to how to set the event up.  Don't do this in a production environment.

Handle the Event

We now need to wire in the action to take when the event fires.  We want to get the latest records from the database and refresh the display:

       private async void Changed(object sender, RecordChangedEventArgs<Job> e)

        {

            var employees = await GetAllJobs();

            await InvokeAsync(StateHasChanged);

        }

This is just a sample.  In a production environment, you would want to centralize these calls and be sure you are not creating a performance bottleneck.  You can do some simple things to improve this such as only getting changes records and refreshing the job collection.  Get only the jobs the user did not change, etc.

Use SignalR to Process Messages

I better and more scalable solution would be to use SignalR to manage the message processing.  There are several good references on how to implement SingalR.  For this project, I simply created a JobHub class that will send out a message to All clients listening for the RefreshJobs message.  Then on the Jobs page, I register for the "RefreshJobs" message and connect to the hub.

    List<Job> jobsList = new List<Job>();

    private HubConnection? hubConnection;

    protected override async Task OnInitializedAsync()

    {

        jobsList = await jobService.GetAllJobs();

        hubConnection = new HubConnectionBuilder()

            .WithUrl(Navigation.ToAbsoluteUri("/jobhub"))

            .Build();

        hubConnection.On<List<Job>>("RefreshJobs", jobs =>

            {

                jobsList = jobs;

                InvokeAsync(StateHasChanged);

            });

        await hubConnection.StartAsync();

    }

How Else Can You Use This? 

This solution is not restricted to Blazor.  Blazor makes it easy to implement because of the Data binding and UI refresh.

You can use this solution to connect to a Redis Cache, a Message broker, a service, etc.  You are only limited to your imagination and how much money you have :-).  

Remember this solution is tied to SQL Server, but many other databases have a similar event base system to be alerted when the database changes.  Firebase is the best by far that I have seen.

Summary

This solution is very personal to me.  I have been solving this problem for over 20 years and to see such a simple yet efficient solution is very exciting.  Most of the companies I work with use SQL Server, that limitation is not a deal breaker.

It is solutions like this and things like Maui that get me excited about building software again.  

[source]