C#使用sqldependency监听SqlServer表数据的变化
sql server设置：ALTER DATABASE <DatabaseName> SET ENABLE_BROKER;语句让相应的数据库启用监听服务
当ALTER DATABASE Databasename SET ENABLE_BROKER; 执行过程中始终不能结束的时候。
ALTER DATABASE DatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE Databasename SET ENABLE_BROKER;
这样可以执行完毕，以便支持SqlDependency特性。
先配置app.config
<connectionStrings >
<add name="SQLConnString" connectionString="Data Source=.;Initial Catalog=***;User ID=***;Password=***" providerName="System.Data.SqlClient"/>
</connectionStrings>
控制台程序
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
static void Main(string[] args)
{
_connStr = ConfigurationManager.ConnectionStrings["SQLConnString"].ToString();
SqlDependency.Start(_connStr);
UpdateGrid();
Console.ReadKey();
}
private static void UpdateGrid() {
using (SqlConnection con = new SqlConnection(_connStr)) {
using (SqlCommand cmd = new SqlCommand("select ID,UserID,[Message] From [dbo].[Messages]", con)) {
cmd.CommandType = CommandType.Text;
con.Open();
SqlDependency sqldep = new SqlDependency(cmd);
sqldep.OnChange += new OnChangeEventHandler(SqlDep_OnChange);
SqlDataReader sdr = cmd.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();
}
namespace Messge
{
class Program
{
private static string _connStr;
static void Main(string[] args)
{
_connStr = ConfigurationManager.ConnectionStrings["SQLConnString"].ToString();
SqlDependency.Start(_connStr);
UpdateGrid();
Console.ReadKey();
}
private static void UpdateGrid() {
using (SqlConnection con = new SqlConnection(_connStr)) {
using (SqlCommand cmd = new SqlCommand("select ID,UserID,[Message] From [dbo].[Messages]", con)) {
cmd.CommandType = CommandType.Text;
con.Open();
SqlDependency sqldep = new SqlDependency(cmd);
sqldep.OnChange += new OnChangeEventHandler(SqlDep_OnChange);
SqlDataReader sdr = cmd.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 SqlDep_OnChange(object sender,SqlNotificationEventArgs e) {
UpdateGrid();
}
}
}
效果:
数据库变化前:
数据库变化后：
