blazor_使用Blazor和SqlTableDependency进行实时HTML页面内容更新

blazor

介绍 (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).

在这个简单的示例中，我们将看到发生SQL Server数据库表更改时如何更新HTML页面，而无需重新加载该页面或从客户端到服务器进行异步调用，而是从服务器获取此HTML刷新内容。服务器使用Blazor服务器端(.NET CORE 3.0)。

背景 (Background)

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

多年前，我发表了一篇有关“ 使用SignalR和SQLTableDependency进行记录更改SQL Server通知 ”的文章。

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.

SignalR使用了上一篇文章，以获得实时更改页面内容的通知。 虽然功能正常，但SignalR在我看来并不如此，因此即时且易于使用。

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.

在Blazor的帮助下，从服务器到HTML页面的通知得到了极大的简化，从而获得了极好的抽象水平：使用Blazor-实际上，我们的代码只是C＃和Razor语法。

使用代码 (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.

假设您有一个报告股票清单的页面，并且其中任何一种价格发生变化时，都需要刷新HTML页面。

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.

在SignalR之前，通常有一个使用Ajax的JavaScript代码，该代码会定期(例如，每5秒一次)向服务器执行GET请求，以检索可能的新价格并将其显示在HTML页面中。

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.

如今， 借助Blazor及其嵌入式SignalR功能，我们可以扭转这一趋势，并让服务器有责任仅在显示一些新价格时才更新HTML页面。

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:

在以下示例中，Blazor将负责更新HTML页面，而SqlTableDependency组件将负责在由于insert ， update或delete而导致表内容发生任何更改时，从SQL Server数据库获取通知：

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

我们必须使用Visual Studio 2019中的适当模板创建.NET CORE 3.0 Blazor Web应用程序。

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

然后，我们安装SqlTableDependency NuGet软件包，该软件包将负责获取有关记录表更改的通知：

PM> Install-Package SqlTableDependency 

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

现在，对于此示例，让我们考虑要监视以下SQL Server表的值：

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:

因此，我们定义了一个C＃模型类，该类映射了我们感兴趣的属性：

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:

现在，我们创建一个包装SqlTableDependency的单例实例，并将记录表更改转发到Blazor页面。 我们开始创建其接口：

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:

现在我们已经设置了数据库记录更改通知，现在是时候实现Blazor组件了。 第一步，我们使用OnInitialized()方法检索所有当前股价，然后下标有关表记录更改的事件通知，以刷新HTML视图：

@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.

表格记录更改事件处理程序仅检查库存是否在显示的列表中，然后插入或更新其Price值。 请注意，HTML将从Blazor自动刷新。 为了更新HTML视图内容，我们不需要向浏览器发送任何通知，也不需要从浏览器向服务器发出任何轮询请求。

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!

请享用！

翻译自: https://www.codeproject.com/Tips/5256345/Real-Time-HTML-Page-Content-Update-with-Blazor-and

blazor