SqlTableDependency是一個組件用來接收資料庫的通知，包含在數據表上該記錄的值的Inserted、Deleted或者Update操作. 備註：原文提供示例代碼下載，但是js寫的有些問題（沒有更新數據），可以參考下文代碼修改一下，修改後的代碼可以接收插入和更新兩個，對刪除沒有處理。 介紹 ...
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 主題：
