/*
 * 注意事项：

1.修改Database设置
alter database set enable_broker with rollback immediate;

2.SQL的查询语法中不能使用 [*] 的方式
[错误的：] select * From [dbo].[alarm_table]

[正确的] select sn,address,create_time From [dbo].[alarm_table]
 */

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SqlDependency_Test
{
    public partial class MainForm : Form
    {
        /* 确认DB有执行 [alter database <dbname> set enable_broker with rollback immediate;] */
        SqlConnection connection = new SqlConnection();
        private static string ConnectionString;

        public MainForm()//构造
        {
            InitializeComponent();
        }

        /* 启动SQL监控 */
        private void btn_Start_Click(object sender, EventArgs e)
        {
            try
            {
                ConnectionString = "Data Source=" + txt_dbSource.Text + ";Initial Catalog=" + txt_dbDatabase.Text + ";User ID=" + txt_dbUser.Text + ";Password=" + txt_dbPassword.Text;
                SqlDependency.Start(ConnectionString);//调用SqlDependency.Start(String strConnectionString)方法，在应用程序端启用依赖监听器。
                SqlDependencyWatch();
                RefreshTable();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        /* 关闭SQL监控 */
        private void btn_Stop_Click(object sender, EventArgs e)
        {
            SqlDependency.Stop(ConnectionString);
        }

        /* 关闭SQL监控 */
        private void MainForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            SqlDependency.Stop(ConnectionString);
        }

        /* 建立SQL监控 */
        private void SqlDependencyWatch()
        {
            string sSQL = "SELECT [ID],[Name],[Sex],[Birth],[Password],[Insert_Time] FROM [dbo].[T_User]";

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand command = new SqlCommand(sSQL, connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(SQLTableOnChange);
                    SqlDataReader sdr = command.ExecuteReader();
                }
            }
        }

        /* 资料表修改触发Event事件处理 */
        void SQLTableOnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependencyWatch();
            RefreshTable();
        }

        /* 重新更新DataGridView显示资料 */
        private void RefreshTable()
        {
            string sSQL = "SELECT TOP 100 [ID],[Name],[Sex],[Birth],[Password],[Insert_Time] FROM [dbo].[T_User]";

            DataTable datatable = new DataTable();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand(sSQL, connection))
                {
                    using (SqlDataAdapter dr = new SqlDataAdapter(sSQL, connection))
                    {
                        dr.Fill(datatable);
                        this.Invoke((EventHandler)(delegate { dgv_Show.DataSource = datatable; }));
                    }
                }
            }
        }
    }
}

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘DB_MyDB‘;

USE DB_MyDB  
GO
ALTER DATABASE DB_MyDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE; 

ALTER DATABASE DB_MyDB  SET ENABLE_BROKER;