使用SqlDependency实时监听SQL server数据库变化并执行事件

sql server设置：ALTER DATABASE <DatabaseName> SET ENABLE_BROKER;语句让相应的数据库启用监听服务，以便支持SqlDependency特性

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private static string conn = "数据库连接语句";
        static SqlDependency dependency;
        private void Form1_Load(object sender, EventArgs e)
        {
            //string sql = "SELECT u_ipaddress FROM WPS_User_M";
            //DataTable dt = SqlHelper.Query(sql).Tables[0];
            //dataGridView1.DataSource = dt;

            //Start和Stop方法
            SqlDependency.Start(conn);
            Update(conn);
        }

        private static void Update(string conn)
        {
            using (
            SqlConnection connection =
            new SqlConnection(conn))
            {
                //此处 要注意 不能使用* 表名要加[dbo] 否则会出现一直调用执行 OnChange
                string sql = "SELECT u_ipaddress FROM dbo.WPS_User_M";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    command.CommandType = CommandType.Text;
                    dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                    //必须要执行一下command
                    command.ExecuteNonQuery();
                    Console.WriteLine(dependency.HasChanges);
                }
            }
        }
        private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine("onchange方法中：" + dependency.HasChanges);
            Console.WriteLine("数据库数据发生变化" + DateTime.Now);
            //这里要再次调用
            Update(conn);
        }
    }
}