功能 :
C# 监听数据库表字段的变化从而触发业务程序的执行!
数据库:
sql server
数据库配置
如果需要监听数据库的数据修改 需要开启数据库的Service Broker
ALTER DATABASE testDemo SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE testDemo SET ENABLE_BROKER;
控制台 测试代码:
using Microsoft.Data.SqlClient;
using System.Data;
class Program
{
private static string connectionString { get; set; } = "Server=10.20.35.176;Database=SMT;User=root;Password=123456;TrustServerCertificate=true;Connect Timeout=30;";
private static void Main(string[] args)
{
SqlDependency.Start(connectionString);
UpdateGrid(connectionString);
Console.Read();
SqlDependency.Stop(connectionString);
}
private static void UpdateGrid(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT WorkOrder FROM [dbo].[T_MF_StationCrossModel]", connection))
{
command.CommandType = CommandType.Text;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(SQLTableOnChange);
SqlDataReader sdr = command.ExecuteReader();
}
}
}
private static void SQLTableOnChange(object sender, SqlNotificationEventArgs e)
{
UpdateGrid(connectionString);
Console.WriteLine(e.Type.ToString());
RefreshTable();//具体业务
}
private static void RefreshTable()
{
string sSQL = "SELECT * FROM [dbo].[T_MF_StationCrossModel]";
DataTable datatable = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(sSQL, connection))
{
using (SqlDataReader sdr = cmd.ExecuteReader())
{
Console.WriteLine();
while (sdr.Read())
{
Console.WriteLine("{0}\tSnum:{1}\t",DateTime.Now.ToString(), sdr["WorkOrder"].ToString());
}
sdr.Close();
}
}
}
}
}
小心有坑
- SqlDependency dependency = new SqlDependency(command);
command 语句很重要 不可以使用 * 进行查询 如果使用 * 会造成不断触发
- SqlCommand command = new SqlCommand(“SELECT WorkOrder FROM [dbo].[T_MF_StationCrossModel]”, connection)
WorkOrder -监听WorkOrder 字段的增删改 可设置多个字段 但是触发条件为或的关系