监测SQLServer数据库中表的数据变化 方案

sqlDependency提供了这样一种能力:当被监测的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件来通知应用程序,从而达到让系统自动更新数据(或缓存)的目的.

快速上手可参见园友的下列文章

http://www.cnblogs.com/xrinehart/archive/2006/07/27/461106.html .NET 2.0 SqlDependency快速上手指南
http://www.cnblogs.com/gesenkof99/archive/2008/12/19/1358584.html 采用SQLServer2005 Broker和SqlDependency类来提供数据更改通知(原创)

https://www.cnblogs.com/lhxsoft/p/6672098.html 转载地址
这里参考他们的文章,自己也做了一个小的示例程序(Sql2005环境)

1.先建一个测试表

CREATE TABLE [dbo].[Messages](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [Message] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 

ON [PRIMARY]
)

然后可以利用Sql2005的管理器,随便输入几条数据

2.控制台程序主要代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestConsole
{
class Program
{
static void Main(string[] args)
{

SqlDependency.Start("server=.;database=PJL;user=sa;pwd=xxx");//传入连接字符串,启动基于数据库的监听
UpdateGrid();

Console.Read();
}

private static void UpdateGrid()
{
using (SqlConnection connection = new SqlConnection("server=.;database=PJL;user=sa;pwd=xxx"))
{
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
using (SqlCommand command = new SqlCommand("select ID,UserID,[Message] From [dbo].[Messages]", connection))
{
command.CommandType = CommandType.Text;
connection.Open();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

SqlDataReader sdr = command.ExecuteReader();
Console.WriteLine();
while (sdr.Read())
{
Console.WriteLine("Id:{0}\tUserId:{1}\tMessage:{2}", sdr["ID"].ToString(), sdr["UserId"].ToString(),

sdr["Message"].ToString());
}
sdr.Close();
}
}
}

private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
UpdateGrid();
}
}
}