[转]使用SignalR和SQLTableDependency进行记录更改的SQL Server通知

周星星9527

发布于 2021-02-03 10:16:09

1.1K0

发布于 2021-02-03 10:16:09

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

原文：https://blog.csdn.net/mzl87/article/details/104342558

源码：https://github.com/christiandelbianco/monitor-table-change-with-mvc-signalR-jquery-sqltabledependency-example

介绍

SqlTableDependency是一个类，用于在指定查询的结果集由于对数据库表执行的任何insert，update或者delete操作而更改时接收通知。

但是，此类不会发送回已更改记录的值。

因此，假设我们要在网页上显示股票值，则对于收到的每个通知，我们都必须执行一个新的完整查询以刷新缓存，然后刷新浏览器。

但是，如果我们愿意的话，一旦某一股票值发生变化，浏览器便会立即显示新的值，而无需刷新？理想情况下，我们想要的是直接从Web服务器接收通知，而没有来自浏览器的任何轮询系统，也没有拉到数据库表。

解决方案是将SignalR与SqlTableDependency：SqlTableDependency结合使用从表中获取通知，然后SignalR将消息发送到网页。

增强功能

SqlTableDependency是通用C＃组件，用于在指定表的内容更改时发送事件。此事件报告操作类型（INSERT/ UPDATE/ DELETE）以及已删除、已插入或已修改的值。该组件的实现是：

SqlTableDependency 对于SQL Server
~~OracleTableDependency~~ ~~对于Oracle~~

怎么运行的

实例化后，此组件将动态生成用于监视表内容的所有数据库对象。对于SqlTableDependency，我们有：

消息类型
消息契约
队列
Service Broker
表触发器
储存程序

一旦SqlTableDependency被释放，所有这些对象都被释放。

Watch Dog

SqlTableDependency具有watchDogTimeOut，可在应用程序突然断开连接的情况下删除那些对象。此超时设置为3分钟，但是在部署阶段可以增加该超时时间。

放置所有这些对象后，SqlTableDependency获取表内容更改的通知，并在包含记录值的C＃事件中转换此通知。

代码

假设一个包含股票值不断变化的SQL Server数据库表：

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

我们将使用以下模型映射这些表列：

public class Stock
{
    public decimal Price { get; set; }
    public string Symbol { get; set; }
    public string Name { get; set; }        
}

接下来，我们安装NuGet软件包：

PM> Install-Package SqlTableDependency

下一步是创建一个自定义hub类，用于SignalR基础架构：

[HubName("stockTicker")]
public class StockTickerHub : Hub
{
    private readonly StockTicker _stockTicker;
 
    public StockTickerHub() :
        this(StockTicker.Instance)
    {
 
    }
 
    public StockTickerHub(StockTicker stockTicker)
    {
        _stockTicker = stockTicker;
    }
 
    public IEnumerable<Stock> GetAllStocks()
    {
        return _stockTicker.GetAllStocks();
    }
}

我们将使用SignalR Hub API处理服务器到客户端的交互。从SignalR Hub类派生的StockTickerHub类将处理从客户端接收连接和方法调用。我们不能将这些函数放在Hub类中，因为Hub实例是瞬时的。Hub将为集线器上的每个操作创建一个类实例，例如从客户端到服务器的连接和调用。因此，该机制可以保存库存数据，更新值并广播必须在单独的类中运行的值更新，您将其命名为StockTicker：

public class StockTicker
{
    // Singleton instance
    private readonly static Lazy<StockTicker> _instance = new Lazy<StockTicker>(
        () => new StockTicker
        (GlobalHost.ConnectionManager.GetHubContext<StockTickerHub>().Clients));
 
    private static SqlTableDependency<Stock> _tableDependency;
 
    private StockTicker(IHubConnectionContext<dynamic> clients)
    {
        Clients = clients;
 
        var mapper = new ModelToTableMapper<Stock>();
        mapper.AddMapping(s => s.Symbol, "Code");
 
        _tableDependency = new SqlTableDependency<Stock>(
            ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString,
            "Stocks",
            mapper);
 
        _tableDependency.OnChanged += SqlTableDependency_Changed;
        _tableDependency.OnError += SqlTableDependency_OnError;
        _tableDependency.Start();
    }
 
    public static StockTicker Instance
    {
        get
        {
            return _instance.Value;
        }
    }
 
    private IHubConnectionContext<dynamic> Clients
    {
        get;
        set;
    }
 
    public IEnumerable<Stock> GetAllStocks()
    {
        var stockModel = new List<Stock>();
 
        var connectionString = ConfigurationManager.ConnectionStrings
        ["connectionString"].ConnectionString;
        using (var sqlConnection = new SqlConnection(connectionString))
        {
            sqlConnection.Open();
            using (var sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandText = "SELECT * FROM [Stocks]";
 
                using (var sqlDataReader = sqlCommand.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        var code = sqlDataReader.GetString(sqlDataReader.GetOrdinal("Code"));
                        var name = sqlDataReader.GetString(sqlDataReader.GetOrdinal("Name"));
                        var price = 
                            sqlDataReader.GetDecimal(sqlDataReader.GetOrdinal("Price"));
 
                        stockModel.Add
                           (new Stock { Symbol = code, Name = name, Price = price });
                    }
                }
            }
        }
 
        return stockModel;
    }
 
    void SqlTableDependency_OnError(object sender, ErrorEventArgs e)
    {
        throw e.Error;
    }
 
    /// <summary>
    /// Broadcast New Stock Price
    /// </summary>
    void SqlTableDependency_Changed(object sender, RecordChangedEventArgs<Stock> e)
    {
        if (e.ChangeType != ChangeType.None)
        {
            BroadcastStockPrice(e.Entity);
        }
    }
 
    private void BroadcastStockPrice(Stock stock)
    {
        Clients.All.updateStockPrice(stock);
    }
 
    #region IDisposable Support
    private bool disposedValue = false; // To detect redundant calls
 
    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            if (disposing)
            {
                _tableDependency.Stop();
            }
 
            disposedValue = true;
        }
    }
 
    ~StockTicker()
    {
        Dispose(false);
    }
 
    // This code added to correctly implement the disposable pattern.
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
 
    #endregion
}

现在是时候查看HTML页面了：

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>SqlTableDependencly with SignalR</title>
</head>
<body>
    <h1>SqlTableDependencly with SignalR</h1>
 
    <div id="stockTable">
        <table border="1">
            <thead style="background-color:silver">
                <tr><th>Symbol</th><th>Name</th><th>Price</th></tr>
            </thead>
            <tbody>
                <tr class="loading"><td colspan="3">loading...</td></tr>
            </tbody>
        </table>
    </div>
 
    <script src="jquery-1.10.2.min.js"></script>
    <script src="jquery.color-2.1.2.min.js"></script>
    <script src="../Scripts/jquery.signalR-2.2.0.js"></script>
    <script src="../signalr/hubs"></script>
    <script src="SignalR.StockTicker.js"></script>
</body>
</html>

以及我们如何管理JavaScript代码中从SignalR返回的数据：

// Crockford's supplant method
if (!String.prototype.supplant) {
    String.prototype.supplant = function (o) {
        return this.replace(/{([^{}]*)}/g,
            function (a, b) {
                var r = o[b];
                return typeof r === 'string' || typeof r === 'number' ? r : a;
            }
        );
    };
}
 
$(function () {
    var ticker = $.connection.stockTicker; // the generated client-side hub proxy
    var $stockTable = $('#stockTable');
    var $stockTableBody = $stockTable.find('tbody');
    var rowTemplate = '<tr data-symbol="{Symbol}"><td>
    {Symbol}</td><td>{Name}</td><td>{Price}</td></tr>';
 
    function formatStock(stock) {
        return $.extend(stock, {
            Price: stock.Price.toFixed(2)
        });
    }
 
    function init() {
        return ticker.server.getAllStocks().done(function (stocks) {
            $stockTableBody.empty();
 
            $.each(stocks, function () {
                var stock = formatStock(this);
                $stockTableBody.append(rowTemplate.supplant(stock));
            });
        });
    }
 
    // Add client-side hub methods that the server will call
    $.extend(ticker.client, {
        updateStockPrice: function (stock) {
            var displayStock = formatStock(stock);
            $row = $(rowTemplate.supplant(displayStock)),
            $stockTableBody.find('tr[data-symbol=' + stock.Symbol + ']').replaceWith($row);
        }
    });
 
    // Start the connection
    $.connection.hub.start().then(init);
});

最后，我们不必忘记注册SignalR路由：

[assembly: OwinStartup(typeof(Stocks.Startup))]
namespace Stocks
{
    public static class Startup
    {
        public static void Configuration(IAppBuilder app)
        {
            // For more information on how to configure your application using OWIN startup, 
            // visit http://go.microsoft.com/fwlink/?LinkID=316888
 
             app.MapSignalR();
        }
    }
}

测试方法

在附件中，有一个简单的Web应用程序，其中包含一个HTML页面，该页面在表格中报告股票值。

要测试，请按照下列步骤操作：

创建一个表为：

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

用一些数据填充表。
运行Web应用程序，然后浏览/SignalR.Sample/StockTicker.html页面。
修改表中的任何数据以在HTML页面上立即获得通知。

参考文献

SignalR：http : //www.asp.net/signalr/overview/getting-started/tutorial-server-broadcast-with-signalr
SqlTableDependency：https : //github.com/christiandelbianco/monitor-table-change-with-sqltabledependency