C# sqlserver监听数据表变化

 

C# 监听数据库表的变化（SqlDependency）
 1 class Program
    {
        static string connectionString = "Server=.;Database=testDemo;User Id=sa;Password=123456";
        static void Main(string[] args)
        {
            SqlDependency.Start(connectionString);//传入连接字符串,启动基于数据库的监听
            UpdateGrid();
            Console.Read();
            SqlDependency.Stop(connectionString);//传入连接字符串,启动基于数据库的监听
        }
        private static void UpdateGrid()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
                using (SqlCommand command = new SqlCommand("SELECT name,age FROM dbo.student", connection))
                {
                    command.CommandType = CommandType.Text;
                    //connection.Open();
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                    using (SqlDataReader sdr = command.ExecuteReader())
                    {
                        Console.WriteLine();
                        while (sdr.Read())
                        {
                            Console.WriteLine("AssyAcc:{0}	Snum:{1}	", sdr["name"].ToString(), sdr["age"].ToString());
                        }
                        sdr.Close();
                    }
                }
            }
        }
        private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change) //只有数据发生变化时,才重新获取并数据
            {
                UpdateGrid();
            }
        }
    }

 开启数据库Service Broker

ALTER DATABASE testDemo SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE testDemo SET ENABLE_BROKER;

转自：https://www.cnblogs.com/duhaoran/p/13047959.html