C# SqlServer使用SqlDependency监控数据库表的变化
- C# SqlServer使用SqlDependency监控数据库表的变化
-
- 实现步骤：
C# SqlServer使用SqlDependency监控数据库表的变化
开发环境: .net / C# (.net core理论上也可以)
数据库：MS SQL Server 2005 以上 （我用的sqlserver2012）
功能：SqlDependency提供了一种机制，当被监听的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件通知应用程序实时更新数据(或缓存)，而不需要定期要求后端。SignalR技术，基本可以实现实时通信。
我的场景:数据每天变化不大，一天不超过十次，但每次都想得到提醒，反映在前台web页面上。
实现步骤：
- 数据库启用 Service Broker 检查数据库是否启用，Service Broker
SELECT is_broker_enabled FROM sys.databases WHERE name = 数据库名 查询结果：is_broker_enabled de 结果是 0代表数据库没有启动 Service Broker 解决办法：
use 数据库名 go ALTER DATABASE 数据库名 SET NEW_BROKER WITH ROLLBACK IMMEDIATE; ALTER DATABASE 数据库名 SET ENABLE_BROKER; 注:两句同时执行，单独执行会显示 非法事务正在回滚。估计回滚已经完成: 100%” 再次查询is_broker_enabled状态为1，数据库未启动 Service Broker成功。
- Webconfig 中启用缓存 在<system.web>节点加入
<caching> <sqlCacheDependency enabled="true" pollTime="1000"> <databases> <add name="PDMCAPPS" connectionStringName="连接字符串的数据库" pollTime="1000"/> </databases> </sqlCacheDependency> </caching> - 在Global.asax增加启用和停止监控
string connectionString = ConfigurationManager.ConnectionStrings["数据库连接字符串名称"].ConnectionString; void Application_Start(object sender, EventArgs e) {
// Code that runs on application startup System.Data.SqlClient.SqlDependency.Start(connectionString); }
void
Application_End
(
object sender, EventArgs e
)
{
// Code that runs on application shutdown System
.Data
.SqlClient
.SqlDependency
.
Stop
(connectionString
)
;
}
- 主程序代码
private static string conn = ConfigurationManager.ConnectionStrings["连接字符串名称"].ConnectionString;
static SqlDependency dependency;
protected void Page_Load(object sender, EventArgs e)
{
SqlDependency.Start(conn); //传入连接字符串,启动基于数据库的监听
if (!IsPostBack)
{
Update(conn);
}
}
//使用SqlDependency监控数据库表变化
private void Update(string conn)
{
using (SqlConnection connection = new SqlConnection(conn))
{
StringBuilder strsql = new StringBuilder();
//对被监控的目标表做简单查询，此处 要注意 不能使用* 表名要加[dbo] 否则会出现一直调用执行 OnChange
strsql.Append(@"sql查询语句，查询目标表");
using (SqlCommand command = new SqlCommand(strsql.ToString(), connection))
{
connection.Open();
command.CommandType = CommandType.Text;
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);//添加监控，检测数据表变化
//必须要执行一下command
command.ExecuteNonQuery();
//Console.WriteLine(dependency.HasChanges);
//connection.Close();
}
}
}
//检测到数据表变化后执行动作
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change)
{
//这里要再次调用
Update(conn);
//刷新前台页面
Response.AddHeader("Refresh", "0");
}
}
/// <summary>
/// SqlServer 监听 控制台代码
/// </summary>
class Program
{
// 数据库连接字符串
const string Conn_String = "Data Source=.;Initial Catalog=tb_CS;Integrated Security=False;User ID=sa;Password=tlk123456;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
static void Main(string[] args)
{
SqlDependency.Start(Conn_String); // 连接指定数据库病打开监听
DataMonitor(); // 开启数据库监控
Console.ReadKey();
}
static void DataMonitor()
{
using (SqlConnection connection = new SqlConnection(Conn_String))
{
using (SqlCommand cmd = new SqlCommand("SELECT id,name,createTime FROM dbo.tb2", connection))
{
cmd.CommandType = CommandType.Text;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += Dependency_OnChange;
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.HasRows) return;
while (reader.Read())
{
Console.WriteLine(string.Format("id:{0} name:{1}, createTime:{2}",
reader["id"].ToString(),
reader["name"].ToString(),
reader["createTime"].ToString()
));
}
reader.Close();
}
}
}
// 当依赖的表格发生数据改变时触发
private static void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
Console.Clear();
Console.WriteLine("有数据改变了!!!!");
//
DataMonitor();
// 给前台发送信息
PostMessageToJava();
}
/// <summary>
/// 给 给前台发送信息
/// </summary>
private static void PostMessageToJava()
{
// Console.WriteLine("数据发送!!!!");
// throw new NotImplementedException();
}
}