For SQL, C # monitors the sqldependency learning notes of a table

Sqldependency provides the ability to automatically trigger the onchange event to notify the application when the data in the monitored database changes, this allows the system to automatically update data (or cache.

For Quick Start, see the following articles by yuanyou.

Http://www.cnblogs.com/xrinehart/archive/2006/07/27/461106.html
. NET 2.0 sqldependency Quick Start Guide
Http://www.cnblogs.com/gesenkof99/archive/2008/12/19/1358584.html
Use the sqlserver2005 broker and sqldependency class to provide data change notifications (original)

Refer to their article here, and I also made a small example Program (sql2005 environment)

1. Create a test table first

View plaincopy to clipboardprint?

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)
   
10. On [primary]
    
11. )

Create Table [DBO]. [messages] (<br/> [ID] [int] Identity (1,1) not null, <br/> [userid] [varchar] (50) Collate chinese_prc_ci_as not null, <br/> [Message] [nvarchar] (256) Collate chinese_prc_ci_as not null, <br/> constraint [pk_messages] primary key clustered <br/> (<br/> [ID] ASC <br/>) with (pad_index = OFF, statistics_norecompute = off, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on) </P> <p> on [primary] <br/>)

Then you can use the sql2005 manager to enter a few pieces of data at will.

2. Main Code of the console Program

View plaincopy to clipboardprint?

1. Using system;
2. Using system. configuration;
   
3. Using system. Data;
   
4. Using system. Data. sqlclient;
   
6. Namespace sqldependencytest
   
7. {
8. Class Program
9. {
10. Private Static
    String _ connstr;
12. Static void main (string [] ARGs)
    
13. {
14. _ Connstr = configurationmanager. connectionstrings ["connstr"]. tostring ();
    
15. Sqldependency. Start (_ connstr); // input the connection string to start a database-based listener.
    
16. Updategrid ();
18. Console. Read ();
19. }
22. Private Static
    Void updategrid ()
23. {
24. Using (sqlconnection connection =
    New sqlconnection (_ connstr ))
25. {
26. // The dependency is based on a table, and the query statement can only be a simple query statement, but cannot contain top or *, and the owner must be specified, that is, similar to [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 );
    
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 (),
    
40. SDR ["message"]. tostring ());
41. }
42. SDR. Close ();
43. }
44. }
45. }
48. Private Static
    Void dependency_onchange (Object sender, sqlnotifeventargs E)
    
49. {
50. Updategrid ();
51. }
52. }
53. }

Using system; <br/> using system. configuration; <br/> using system. data; <br/> using system. data. sqlclient; </P> <p> namespace sqldependencytest <br/> {<br/> class Program <br/>{< br/> Private Static string _ connstr; </P> <p> static void main (string [] ARGs) <br/>{< br/> _ connstr = configurationmanager. connectionstrings ["connstr"]. tostring (); <br/> sqldependency. start (_ connstr); // input the connection string to start a database-based listener <br/> updategrid (); </P> <p> console. read (); <br/>}</P> <p> Private Static void updategrid () <br/>{< br/> using (sqlconnection connection = new sqlconnection (_ connstr) <br/>{< br/> // The dependency is based on a table, in addition, the query statement can only be a simple query statement, and cannot contain top or *. You must also specify the owner, that is, similar to [DBO]. [] <br/> using (sqlcommand command = new sqlcommand ("select ID, userid, [Message] from [DBO]. [messages] ", connection) <br/>{< br/> command. commandtype = commandtype. text; <br/> connection. open (); <br/> sqldependency dependency = new sqldependency (command); <br/> dependency. onchange + = new onchangeeventhandler (dependency_onchange); </P> <p> sqldatareader SDR = command. executereader (); <br/> console. writeline (); <br/> while (SDR. read () <br/>{< br/> console. writeline ("ID: {0} \ tuserid: {1} \ tmessage: {2}", SDR ["ID"]. tostring (), SDR ["userid"]. tostring (), </P> <p> SDR ["message"]. tostring (); <br/>}< br/> SDR. close (); <br/>}</P> <p> Private Static void dependency_onchange (Object sender, sqlnotificationeventargs E) <br/>{< br/> updategrid (); <br/>}< br/>}

3. Test Run

After running the console program, all data in the [messages] Table is output. Do not close the console program, modify the data in the [messages] Table directly in the sql2005 Manager (for example, add/delete). Then, you can view the console and find that new data is automatically output.

If: When sqldependency is used, the system prompts that service broker is not enabled for the current database.

Please refer to my article: http://blog.csdn.net/andrew_wx/article/details/6947317

Note: In a web application, the server can be activated only when a page is requested by a browser. Therefore, once the page is processed and displayed in the browser, it remains unchanged, the onchange event is never triggered.

Finally, I would like to recommend an article about the elders in the garden:
Http://www.cnblogs.com/artech/archive/2008/08/11/1265055.html
It is a comprehensive application that combines the cache application block of the Enterprise Library with sqldependency. It is well written. It is strongly recommended that you want to use the cache.

 

This article goes to: http://blog.csdn.net/andrew_wx/article/details/6947329#comments