关于SQL中，C#监视某张表SqlDependency学习笔记 （转）

sqlDependency提供了这样一种能力:当被监测的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件来通知应用程序,从而达到让系统自动更新数据(或缓存)的目的.

快速上手可参见园友的下列文章

http://www.cnblogs.com/xrinehart/archive/2006/07/27/461106.html .NET 2.0 SqlDependency快速上手指南
http://www.cnblogs.com/gesenkof99/archive/2008/12/19/1358584.html 采用SQLServer2005 Broker和SqlDependency类来提供数据更改通知(原创)

这里参考他们的文章,自己也做了一个小的示例程序(Sql2005环境)

1.先建一个测试表

view plain copy to clipboard print ?

1. CREATE TABLE [dbo].[Messages]( 
2.     [ID] [int] IDENTITY(1,1) NOT NULL, 
3.     [UserID] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, 
4.     [Message] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NOT NULL, 
5. CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED  
6. ( 
7.     [ID] ASC 
8. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)  
9.  
10. ON [PRIMARY] 
11. ) 

CREATE TABLE [dbo].[Messages]( [ID] [int] IDENTITY(1,1) NOT NULL, [UserID] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, [Message] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )

然后可以利用Sql2005的管理器,随便输入几条数据

2.控制台程序主要代码

view plain copy to clipboard print ?

1. using System; 
2. using System.Configuration; 
3. using System.Data; 
4. using System.Data.SqlClient; 
5.  
6. namespace SqlDependencyTest 
7. { 
8.     class Program 
9.     { 
10.         private static string _connStr; 
11.  
12.         static void Main(string[] args) 
13.         { 
14.             _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString(); 
15.             SqlDependency.Start(_connStr);//传入连接字符串,启动基于数据库的监听 
16.             UpdateGrid(); 
17.  
18.             Console.Read(); 
19.         } 
20.  
21.  
22.         private static void UpdateGrid() 
23.         { 
24.             using (SqlConnection connection = new SqlConnection(_connStr)) 
25.             { 
26.                 //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[] 
27.                 using (SqlCommand command = new SqlCommand("select ID,UserID,[Message] From [dbo].[Messages]", connection)) 
28.                 { 
29.                     command.CommandType = CommandType.Text; 
30.                     connection.Open(); 
31.                     SqlDependency dependency = new SqlDependency(command); 
32.                     dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); 
33.  
34.                    SqlDataReader sdr = command.ExecuteReader(); 
35.                    Console.WriteLine(); 
36.                    while (sdr.Read()) 
37.                    { 
38.                        Console.WriteLine("Id:{0}\tUserId:{1}\tMessage:{2}",sdr["ID"].ToString(), sdr["UserId"].ToString(),  
39.  
40. sdr["Message"].ToString()); 
41.                    } 
42.                    sdr.Close(); 
43.                 } 
44.             } 
45.         } 
46.  
47.  
48.         private static void dependency_OnChange(object sender, SqlNotificationEventArgs e) 
49.         { 
50.             UpdateGrid(); 
51.         } 
52.     } 
53. } 

using System; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace SqlDependencyTest { class Program { private static string _connStr; static void Main(string[] args) { _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString(); SqlDependency.Start(_connStr);//传入连接字符串,启动基于数据库的监听 UpdateGrid(); Console.Read(); } 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(); } } }

3.测试运行

运行该控制台程序后,会输出[Messages]表的所有数据,这时不要关闭控制台程序,直接在Sql2005的管理器里对[Messages]表的数据做些修改(比如新增/删除等),再看一下控制台,会发现自动重新输出了新的数据.

若出现：使用SqlDependency时提示当前数据库未启用Service broker

请参考我的这篇文章：http://blog.csdn.net/andrew_wx/article/details/6947317

注意:如果在web应用中,因为页面必须通过浏览器请求才能激活服务端的处理,所以页面一旦处理完成并显示到浏览器后,放着不动的情况下,OnChange事件始终是得不到触发的.

最后再推荐一篇园子里高人的文章:
http://www.cnblogs.com/artech/archive/2008/08/11/1265055.html 是结合Enterprise Library的缓存应用程序块与SqlDependency的综合应用,写得很不错,强烈推荐想使用缓存的朋友看看.

 

本文转至：http://blog.csdn.net/andrew_wx/article/details/6947329#comments