Click here to Skip to main content
14,454,989 members

Real Time HTML Page Content Update with Blazor and SqlTableDependency

Rate this:
4.85 (22 votes)
Please Sign up or sign in to vote.
4.85 (22 votes)
31 Jan 2020CPOL
In this simple example, we are going to see how to update an HTML page when a SQL Server table change occurs, without the need to reload the page or make asynchronous calls from the client to the server.

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.

Image 1

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

  • 22nd January, 2020: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Christian Del Bianco
Software Developer
Italy Italy
Email: christian.delbianco@gmail.com
Skype: christian.delbianco

Comments and Discussions

 
QuestionGreat article. Pin
Member 195199014-Feb-20 1:00
MemberMember 195199014-Feb-20 1:00 
SuggestionHow about watching multiple tables? Pin
Paul Meems10-Feb-20 4:13
MemberPaul Meems10-Feb-20 4:13 
GeneralRe: How about watching multiple tables? Pin
Member 1474286714-Feb-20 5:50
MemberMember 1474286714-Feb-20 5:50 
QuestionNice post Pin
Mou_kol31-Jan-20 10:59
MemberMou_kol31-Jan-20 10:59 
QuestionUpdate table Pin
mag1330-Jan-20 12:25
Membermag1330-Jan-20 12:25 
QuestionDelete Record Pin
Member 1473115130-Jan-20 12:06
MemberMember 1473115130-Jan-20 12:06 
AnswerRe: Delete Record Pin
BDisp3-Feb-20 8:52
MemberBDisp3-Feb-20 8:52 
GeneralRe: Delete Record Pin
Member 147311513-Feb-20 9:50
MemberMember 147311513-Feb-20 9:50 
GeneralMy vote of 5 Pin
Carsten V2.024-Jan-20 5:01
MemberCarsten V2.024-Jan-20 5:01 
QuestionExcellent! Pin
bdaunt@live.com24-Jan-20 0:39
Memberbdaunt@live.com24-Jan-20 0:39 
AnswerRe: Excellent! Pin
Christian Del Bianco25-Jan-20 3:59
MemberChristian Del Bianco25-Jan-20 3:59 
GeneralRe: Excellent! Pin
Red Feet30-Jan-20 23:12
MemberRed Feet30-Jan-20 23:12 
GeneralRe: Excellent! Pin
Christian Del Bianco31-Jan-20 3:30
MemberChristian Del Bianco31-Jan-20 3:30 
PraiseOutstanding! Pin
Gary Schumacher23-Jan-20 21:20
MemberGary Schumacher23-Jan-20 21:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 22 Jan 2020

Stats

20.5K views
541 downloads
34 bookmarked