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

周星星9527

发布于 2021-02-03 09:59:26

92200

代码可运行

文章被收录于专栏：javascript趣味编程javascript趣味编程

运行总次数：0

代码可运行

根据如下博文进行了测试，记录如下，之前有过转载。关键是数据库安装和连接字符串配置：https://blog.csdn.net/cunhan4654/article/details/108224371

udl文件链接字符串如下：

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ass

NaviCat连接配置：

内容：

ASP.NET的连接字符串appsettings.json：

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

模型：

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

T-SQL语句

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

相关页面index.razor：

@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
        this.StockService.OnStockChanged += this.StockChanged;
        this.stocks = this.StockService.GetCurrentValues();
    }

    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;
    }
}

相关事件接口ITableChangeBroadcastService.cs:

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();
    }
}

事件处理TableChangeBroadcastService.cs：

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();
        }

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

        /// <summary>
        /// This method is use to populate the HTML view when it is rendered for the firt time
        /// </summary>
        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();
        }
    }
}

注册服务Startup.cs：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Components;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using BlazorApp1.Service;

namespace BlazorApp1
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddRazorPages();
            services.AddServerSideBlazor();
            services.AddSingleton<ITableChangeBroadcastService, TableChangeBroadcastService>();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Error");
            }

            app.UseStaticFiles();

            app.UseRouting();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapBlazorHub();
                endpoints.MapFallbackToPage("/_Host");
            });
        }
    }
}

运行结果：