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

Christian Del Bianco

4.73/5 (41投票s)

2015 年 9 月 13 日

CPOL

3分钟阅读

137102

1460

SqlTableDependency是一个用于接收数据库通知的组件，这些通知包含在数据库表中插入、删除或更新的记录值。

引言

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

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

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

但是，如果我们希望一旦一个股票值发生变化，浏览器立即显示新值，而无需刷新怎么办？理想情况下，我们希望直接从 Web 服务器接收通知，而无需浏览器的任何轮询系统，也无需拉取数据库表。

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

增强功能

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

SQL Server 的 SqlTableDependency
~~Oracle 的 OracleTableDependency~~

工作原理

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

消息类型
消息合同
队列
Service Broker
表触发器
存储过程

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

看门狗

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

历史

2017 年 7 月 3 日：初始版本