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();
}
}
}

效果:

数据库变化前:

 

 

 

 数据库变化后：