C#+SqlServer使用SqlDependency监听数据库表变化

C#+SqlServer使用SqlDependency监听数据库表变化

开发环境: .net / C# (.net core理论上也可以)

数据库：MS SQL Server 2005 以上 （我用的sqlserver2012）

功能：SqlDependency提供了一种机制，当被监听的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件来通知应用程序,从而达到让应用程序实时更新数据(或缓存)的目的，而不用定期请求后端，如果加上SignalR技术，基本可以实现实时通信。

我的场景：每天数据变化少，一天不超过十次，但是每次都希望得到提醒，并反应到前台web页面上。

实现步骤：

1. 数据库启用 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成功。

2. Webconfig 中启用缓存
   在<system.web>节点加入

<caching>
      <sqlCacheDependency enabled="true" pollTime="1000">
        <databases>
          <add name="PDMCAPPS" connectionStringName="数据库连接字符串" pollTime="1000"/>
        </databases>
      </sqlCacheDependency>
    </caching>

3. 在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);


   }

4. 主程序代码

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();
        }
    }