Real Time HTML Page Content Update with Blazor and SqlTableDependency

• Download source - 1.3 MB

Introduction

In this simple example, we are going to see how to update an HTML page when a SQL Server database table change occurs without the need to reload the page or make asynchronous calls from the client to the server, but getting this HTML refresh content from the server using Blazor server side (.NET CORE 3.0).

Background

Years ago, I published an article about "SQL Server Notifications on Record Change with SignalR and SQLTableDependency".

The previous article, to obtain notifications that altered the content of the page in real time, was used by SignalR. Although functional, SignalR is not in my opinion, so immediate and easy to use.

With the help of Blazor, the notifications from the server to the HTML pages are greatly simplified obtaining a fantastic level of abstraction: using Blazor - in fact - our code is only C# and Razor syntax.

Using the Code

Let’s assume you have a page reporting a list of stocks, and any time one of these prices change, the HTML page needs to be refreshed.

Before SignalR, it was common to have a JavaScript code using Ajax that periodically (for example, every 5 seconds) executes a GET request to the server, in order to retrieve possible new prices and display them in the HTML page.

Today, thanks to Blazor and its embedded SignalR functionality, we can invert this trend, and give the responsibility to the server to update the HTML page only when there is some new price to display.

In the following example, Blazor will take care of updating the HTML page, while SqlTableDependency component will take care of getting notification from SQL Server database anytime the table content will be changed, due to an insert, update or delete:

We must create a .NET CORE 3.0 Blazor web application, using the proper template from Visual Studio 2019.

Then, we install the SqlTableDependency NuGet package, that will take care of getting notifications on record table changes:

PM> Install-Package SqlTableDependency 

Now, for this example, let’s consider we want to monitor values of the following SQL Server table:

CREATE TABLE [dbo].[Stocks](
    [Code] [nvarchar](50) NULL,
    [Name] [nvarchar](50) NULL,
    [Price] [decimal](18, 0) NULL
) ON [PRIMARY]

For this reason, we define a C# model class mapping the properties we are interested to:

namespace BlazorApp1.Models
{
    public class Stock
    {
        public decimal Price { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
    }
}

Now we create a singleton instance wrapping SqlTableDependency and forwarding record table changes to Blazor page. We start creating its interface:

using BlazorApp1.Models;
using System;
using System.Collections.Generic;

namespace BlazorApp1.Service
{
    public delegate void StockChangeDelegate(object sender, StockChangeEventArgs args);

    public class StockChangeEventArgs : EventArgs
    {
        public Stock NewValue { get; }
        public Stock OldValue { get; }

        public StockChangeEventArgs(Stock newValue, Stock oldValue)
        {
            this.NewValue = newValue;
            this.OldValue = oldValue;
        }
    }

    public interface ITableChangeBroadcastService : IDisposable
    {
        event StockChangeDelegate OnStockChanged;
        IList<Stock> GetCurrentValues();
    }
}

And then its implementation:

using BlazorApp1.Models;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using TableDependency.SqlClient;
using TableDependency.SqlClient.Base.EventArgs;

namespace BlazorApp1.Service
{
    public class TableChangeBroadcastService : ITableChangeBroadcastService
    {
        private const string TableName = "Stocks";
        private SqlTableDependency<Stock> _notifier;
        private IConfiguration _configuration;

        public event StockChangeDelegate OnStockChanged;

        public TableChangeBroadcastService(IConfiguration configuration)
        {
            _configuration = configuration;

            // SqlTableDependency will trigger an event 
            // for any record change on monitored table  
            _notifier = new SqlTableDependency<Stock>(
                 _configuration["ConnectionString"], 
                 TableName);
            _notifier.OnChanged += this.TableDependency_Changed;
            _notifier.Start();
        }

        // This method will notify the Blazor component about the stock price change stock
        private void TableDependency_Changed(object sender, RecordChangedEventArgs<Stock> e)
        {
            this. OnStockChanged(this, new StockChangeEventArgs(e.Entity, e.EntityOldValues));
        }

        // This method is used to populate the HTML view 
        // when it is rendered for the first time
        public IList<Stock> GetCurrentValues()
        {
            var result = new List<Stock>();

            using (var sqlConnection = new SqlConnection(_configuration["ConnectionString"]))
            {
                sqlConnection.Open();

                using (var command = sqlConnection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM " + TableName;
                    command.CommandType = CommandType.Text;

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                result.Add(new Stock
                                {
                                    Code = reader.GetString(reader.GetOrdinal("Code")),
                                    Name = reader.GetString(reader.GetOrdinal("Name")),
                                    Price = reader.GetDecimal(reader.GetOrdinal("Price"))
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }

        public void Dispose()
        {
            _notifier.Stop();
            _notifier.Dispose();
        }
    }
}

Now that we have set up the database record change notification, it is time to implement our Blazor component. As a first step, we retrieve all current stock price in OnInitialized() method and then we subscript to event notification about table record change, in order to refresh the HTML view:

@page "/"
@using BlazorApp1.Models
@using BlazorApp1.Service

@inject ITableChangeBroadcastService StockService
@implements IDisposable

<h1>Stock prices</h1>

<p>Immediate client notification on record table change with Blazor</p>

<table class="table">
    <thead>
        <tr>
            <th>Code</th>
            <th>Name</th>
            <th>Price</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var stock in stocks)
        {
            <tr>
                <td>@stock.Code</td>
                <td>@stock.Name</td>
                <td>@stock.Price</td>
            </tr>
        }
    </tbody>
</table>

@code {
    IList<Stock> stocks;

    protected override void OnInitialized()
    {
        // Subscription to table record change events
        this.StockService.OnStockChanged += this.StockChanged;
        this.stocks = this.StockService.GetCurrentValues();
    }

    // The event handler, will update the HTML view according to new stock value
    private async void StockChanged(object sender, StockChangeEventArgs args)
    {
        var recordToupdate = this.stocks.FirstOrDefault(x => x.Code == args.NewValue.Code);

        if (recordToupdate == null)
        {
            this.stocks.Add(args.NewValue);
        }
        else
        {
            recordToupdate.Price = args.NewValue.Price;
        }

        await InvokeAsync(() =>
        {
            base.StateHasChanged();
        });
    }

    public void Dispose()
    {
        this.StockService.OnStockChanged -= this.StockChanged;
    }
}

The table record change event handler simply checks if the stock is in the displayed list and then inserts or updates its Price value. Note here that the HTML will be refreshed automatically from Blazor. We do not need to send any notification to browsers as well as we do not need make any polling request from browsers to server, in order to update the HTML view content.

To conclude, we define the dependency resolution as singleton:

namespace BlazorApp1
{
    public class Startup
    {
        …
        …
        public void ConfigureServices(IServiceCollection services)
        {
            …
            services.AddSingleton<ITableChangeBroadcastService, TableChangeBroadcastService>();
            …
        }
}

And… don’t forget to set the database connection string!

{
    "ConnectionString": "Data Source=***; initial catalog=***; User ID=sa;Password=***"
}

Enjoy!

History

• 22^nd January, 2020: Initial version