欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-08-31 16:58:24
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)。

· 语句不能引用派生表、临时表或表变量。

· 语句不能从其他数据库或服务器中引用表或视图。

· 语句不能包含子查询、外部联接或自联接。

· 语句不能引用下列大型对象类型:textntext 和 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: 

· sqltabledependency: 

· msdn 主题:

    • sqldependency: 
    • sql server 中的查询通知:
    • 为通知创建查询: