数据库缓存依赖

建立依赖

cd C:\Windows\Microsoft.NET\Framework\v4.0.30319

aspnet_regsql -C "Data Source=xxx;Initial Catalog=basic_emrcp;User ID=xx;Password=xxx;" -ed -et -t "table_name"  

开启broker

ALTER DATABASE [basic_emrcp] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

GO
ALTER DATABASE [basic_emrcp] SET DISABLE_BROKER ;

GO

删除依赖

aspnet_regsql -C "Data Source=192.168.101.53;Initial Catalog=basic_emrcp;User ID=sa;Password=test;" -dd 

 

        private static void UpdateGrid()
        {
            using (SqlConnection connection = new SqlConnection(_connStr))
            {
                //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
                using (SqlCommand command = new SqlCommand("select ID,UserID,[Message] From [dbo].[Messages]", connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
 
                   SqlDataReader sdr = command.ExecuteReader();
                   Console.WriteLine();
                   while (sdr.Read())
                   {
                       Console.WriteLine("Id:{0}\tUserId:{1}\tMessage:{2}",sdr["ID"].ToString(), sdr["UserId"].ToString(), 
 
sdr["Message"].ToString());
                   }
                   sdr.Close();
                }
            }
        }
 
 
        private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            UpdateGrid();
        }