使用SignalR和SQLTableDependency跟踪数据库中记录的变动

介绍

SqlDependency 是用来接收数据表中指定的结果集在insert、update 或者delete 操作而发生变化的通知的一个类库.不过，这个类不会回送记录更改的值.所以，假如我想在web页面中展示股票的值，收到每个通知后，我们都需要执行一个新的查询来刷新缓存并刷新浏览器.如果我们股票值一发生变化浏览器就立马显示新的值，而不需要刷新浏览器，理想情况下我们想从web服务器中接收通知，而不是从浏览器进行轮询和从数据库拉取数据.

解决方案是使用SignalR结合SqlTableDependency来处理; SqlTableDependency从数据库获取通知，接着使用SignalR给web页面发送通知.

 

增强实现

TableDependency 是一个SQLDependency增强版的开源C#组件，当指定的表内容更改后用来发送事件。这个事件报告操作类型((INSERT/UPDATE/DELETE)以及变化删除、插入或修改的值。组件的实现包含：

• SqlTableDependency for SQL Server
• OracleTableDependency for Oracle

TableDependency可以通过Nuget来进行加载。

如何工作

当实例化时，动态生成组件对象用来监视数据表的所有数据库对象。在SqlTableDependency中，包含:

· Message Types

· Message Contract

· Queue

· Service Broker

· Table Trigger

· Stored Procedure

在应用程序突然退出情况下，用来清理创建的对象(也就是说，当应用程序终止时，没有处理SqlTableDependency对象)

数据库中生成的内容截图：

所有这些对象会在SqlTableDependency 释放的时候一次性释放.

监视器

SqlTableDependency 内有一个watchDogTimeOut 对象，负责在程序突然断开的时候移除对象。默认的超时时间是3分钟，在发布阶段，这个时间还可以增加

通过上述的一系列对象，当表内容变化时，SqlTableDependency 获取到通知并且发送包含记录值的通知到C#事件.

代码

假设有一个股票值的表，里面的股票价格会频繁变化：

CREATE TABLE [dbo].[Stocks](

[Code] [nvarchar](50) NULL,

[Name] [nvarchar](50) NULL,

[Price] [decimal](18, 0) NULL

) ON [PRIMARY]

我们把数据表的列映射到下面的model：

public class Stock

{

    public decimal Price { get; set; }

    public string Symbol { get; set; }

    public string Name { get; set; }

}

接下来，需要使用Nuget安装程序包：

PM> Install-Package SqlTableDependency

下一步，创建一个SignlaR的Hub类，继承与SignalR的Hub类：

 

[HubName("stockTicker")]

public class StockTickerHub : Hub

{

    private readonly StockTicker _stockTicker;

    public StockTickerHub() : this(StockTicker.Instance)

    {

    }

    public StockTickerHub(StockTicker stockTicker)

    {

        _stockTicker = stockTicker;

    }

    public IEnumerable GetAllStocks()

    {

        return _stockTicker.GetAllStocks();

    }

    public void alertAll()

    {

        Clients.All.testSignalR();

    }

}

我们将使用SignalR Hub API来处理服务器端-客户端的交互。StockTickerHub 类派生自SignalR的Hub类，用来处理客户端的连接和方法调用。不能把这些方法放在Hub类里面，因为Hub 的实例的生命周期为transient（短暂的）。一个Hub 类会为每一个客户端连接和方法调用创建实例。所以要保存股票数据，更新价格和广播更新价格需要运行在一个独立的类，这里命名为StockTicker:

 

    public class StockTicker

    {

        // Singleton instance

        private readonly static Lazy _instance = new Lazy(

            () => new StockTicker(GlobalHost.ConnectionManager.GetHubContext().Clients));

        private static SqlTableDependency _tableDependency;

        private StockTicker(IHubConnectionContext clients)

        {

            Clients = clients;

            var mapper = new ModelToTableMapper();

            mapper.AddMapping(s => s.Symbol, "Code");

            var connStr = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

            //此方法有11个重载,可以只指定连接字符串和表名

            _tableDependency = new SqlTableDependency(connStr, "Stocks", mapper);

            _tableDependency.OnChanged += SqlTableDependency_Changed;

            _tableDependency.OnError += SqlTableDependency_OnError;

            _tableDependency.Start();

        }

        public static StockTicker Instance

        {

            get

            {

                return _instance.Value;

            }

        }

        private IHubConnectionContext Clients

        {

            get;

            set;

        }

        public IEnumerable GetAllStocks()

        {

            var stockModel = new List();

            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;

        }

        private void SqlTableDependency_OnError(object sender, ErrorEventArgs e)

        {

            throw e.Error;

        }

        /// 

        /// Broadcast New Stock Price

        /// 

        private void SqlTableDependency_Changed(object sender, RecordChangedEventArgs 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 IDisposable Support

    }

 

现在来看一下HTML页面:

    SqlTableDependencly with SignalR

    SqlTableDependencly with SignalR