功能 ：

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 字段的增删改 可设置多个字段 但是触发条件为或的关系