Go SqlDependency Study Notes

This article was reproduced from: http://www.cnblogs.com/yjmyzz/archive/2009/06/14/1502921.html

SqlDependency provides the ability to automatically update the data (or cache) for the system by triggering the OnChange event to notify the application when the data in the monitored database changes sqldependency.

Quick start to see the following articles of the park friends

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 uses SQLServer2005 Broker and SqlDependency classes to provide notification of data changes Original

Refer to their article here, oneself also made a small sample program (SQL2005 environment)

1. Build a test table first

CREATE TABLE [dbo]. [Messages] (
[ID] [int] IDENTITY (*) Not NULL,
[UserID] [varchar] (COLLATE) chinese_prc_ci_as not NULL,
[Message] [nvarchar] (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 = O N

On [PRIMARY]
)

You can then use the Sql2005 Manager to enter a few random data

2. Main code of the console program

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);//incoming connection string, initiating database-based snooping
UpdateGrid ();

Console.read ();
}


private static void UpdateGrid ()
{
using (SqlConnection connection = new SqlConnection (_CONNSTR))
{
Dependencies are based on a table, and the query statement can only be a simple query statement, cannot take top or *, and must specify the owner, which is similar to [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. Test run

After running the console program, all the data of the [Messages] table is output, do not close the console program, directly in the Sql2005 manager of the [Messages] table data to make some changes (such as new/delete, etc.), and then look at the console, you will find automatically re-output new data.

Note: If you are in a web app, because the page has to be requested by the browser to activate the processing on the server, the onchange event is always not triggered when the page is processed and displayed to the browser and is left motionless.

Finally, we recommend an article in the garden:
Http://www.cnblogs.com/artech/archive/2008/08/11/1265055.html is an integrated application of the cache Application block and SqlDependency in Enterprise Library, Well written, highly recommended to use cached friends to see.

If it appears: When using SqlDependency, the current database is not enabled for Service Broker

Workaround:

ALTER DATABASE SET  with ROLLBACK IMMEDIATE;   ALTER DATABASE SET Enable_broker;  

, it's OK.

Go SqlDependency Study Notes