SqlTableDependency是一个组件用来接收数据库的通知，包含在数据表上该记录的值的Inserted、Deleted或者Update操作.
备注：原文提供示例代码下载，但是js写的有些问题（没有更新数据），可以参考下文代码修改一下，修改后的代码可以接收插入和更新两个，对删除没有处理。
介绍
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<Stock> 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<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");
var connStr = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
//此方法有11个重载,可以只指定连接字符串和表名
_tableDependency = new SqlTableDependency<Stock>(connStr, "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;
}
private void SqlTableDependency_OnError(object sender, ErrorEventArgs e)
{ throw e.Error;
}
/// <summary>
/// Broadcast New Stock Price
/// </summary>
private 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 IDisposable Support
}
现在来看一下HTML页面:
<!DOCTYPE html>
<html>
<head>
<title>SqlTableDependencly with SignalR</title>
<link href="StockTicker.css" rel="stylesheet" />
</head>
<body>
<h1>SqlTableDependencly with SignalR</h1>
<input type="button" value="测试SignalR" id="btnTest" />
<div id="stockTable">
<table border="1">
<thead style="
<tr><th>Code</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>
下面是js处理SignalR中方法调用和返回的数据的代码:
/// <reference path="../Scripts/jquery-1.10.2.js" />
/// <reference path="../Scripts/jquery.signalR-2.1.1.js" />
// Crockford's supplant method (poor man's templating)
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>'; $("#btnTest").click(function () { ticker.server.alertAll();
});
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));
var $oldRow = $stockTableBody.find('tr[data-symbol=' + stock.Symbol + ']'); if ($oldRow.length) { $oldRow.replaceWith($row);
} else { $stockTableBody.append($row);
}
}
});
$.extend(ticker.client, { testSignalR: function () { alert("服务器发通知了"); }
});
// Start the connection
$.connection.hub.start().then(init);
});
最后，不要忘记在StartUp中注册SignalR的路由:
[assembly: OwinStartup(typeof(Stocks.Startup))]
namespace Stocks
{ public static class Startup
{ public static void Configuration(IAppBuilder app)
{ app.MapSignalR();
}
}
}
如何测试
在附件中，包含一个简单的web应用，包含一个页面，用来展示股票价格变动的表格.
如果进行测试，按如下步骤操作:
· 首先为目标数据库执行语句：
ALTER DATABASE MyDatabase SET ENABLE_BROKER
· 创建数据表:
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 页面.
· 修改数据表中的数据，可以看到表格中的数据会随之更新.
补充
关于SqlTableDependency
微软本身提供了一个数据变动通知的实现：SqlDependency,但如作者所说，只能得到变动通知，并不知道发生了什么变化.SqlDependency支持的 SELECT 语句如下,详细介绍：查看
满足下列要求的 SELECT 语句支持查询通知：
· 必须显式说明 SELECT 语句中提取的列，并且表名必须限定为两部分组成的名称。注意，这意味着语句中引用的所有表都必须处于同一数据库中。
· 语句不能使用星号 (*) 或 table_name.* 语法指定列。
· 语句不能使用未命名列或重复的列名。
· 语句必须引用基表。
· 语句不能引用具有计算列的表。
· 在 SELECT 语句中提取的列不能包含聚合表达式，除非语句使用 GROUP BY 表达式。提供 GROUP BY 表达式时，选择列表便可以包含聚合函数 COUNT_BIG() 或 SUM()。但是，不能为可为空的列指定 SUM()。语句不能指定 HAVING、CUBE 或 ROLLUP。
· 在用作简单表达式的 SELECT 语句中提取的列不能多次显示。
· 语句不能包含 PIVOT 或 UNPIVOT 运算符。
· 语句不能包含 UNION、INTERSECT 或 EXCEPT 运算符。
· 语句不能引用视图。
· 语句不能包含下列任意一个：DISTINCT、COMPUTE、COMPUTE BY 或 INTO。
· 语句不能引用服务器全局变量 (@@variable_name)。
· 语句不能引用派生表、临时表或表变量。
· 语句不能从其他数据库或服务器中引用表或视图。
· 语句不能包含子查询、外部联接或自联接。
· 语句不能引用下列大型对象类型：text、ntext 和 image。
· 语句不能使用 CONTAINS 或 FREETEXT 全文谓词。
· 语句不能使用行集函数，包括 OPENROWSET 和 OPENQUERY。
· 语句不能使用下列任何一个聚合函数：AVG、COUNT(*)、MAX、MIN、STDEV、STDEVP、VAR 或 VARP。
· 语句不能使用任何具有不确定性的函数，包括排名函数和开窗函数。
· 语句不能包含用户定义聚合。
· 语句不能引用系统表或视图，包括目录视图和动态管理视图。
· 语句不能包含 FOR BROWSE 信息。
· 语句不能引用队列。
· 语句不能包含无法更改和无法返回结果的条件语句（如 WHERE 1=0）。
· 语句不能指定 READPAST 锁提示。
· 语句不能引用任何 Service Broker QUEUE。
· 语句不能引用同义词。
· 语句不能具有基于 double/real 数据类型的比较或表达式。
· 语句不得使用 TOP 表达式。
SqlTableDependency 是一个增强的.NET SqlDepenency其优势在于包含了Insert、Update以及Delete的记录的值，以及在表上执行的DML操作(Insert/Delete/Update)。这是与.NET SqlDepenency最大的差异,.NET SqlDepenency没有告诉你哪些数据在数据库上发生了更改。
PS: 如果想要使用数据库通知，必须在数据库中启用Server Broker服务，可以执行如下语句: ALTER DATABASE MyDatabase SET ENABLE_BROKER
使用SqlTableDependency 的步骤：
1. 创建一个SqlTableDependency 实例，并传入连接字符串、表名等参数
2. 订阅SqlTableDependency 的OnChange事件
3. 调用Start()方法开始接收通知
4. 调用Stop()方法停止接收通知
引用
· SignalR: http://www.asp.net/signalr/overview/getting-started/tutorial-server-broadcast-with-signalr
· SqlTableDependency: https://tabledependency.codeplex.com/
· MSDN 主题：
-
- SqlDependency： https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqldependency(v=vs.110).aspx
-
- SQL Server 中的查询通知：https://msdn.microsoft.com/zh-cn/library/t9x04ed2(v=vs.110).aspx
- 为通知创建查询：https://msdn.microsoft.com/zh-cn/library/ms181122(v=sql.105).aspx
