C # Real-time monitoring database SqlDependency

http://blog.csdn.net/idays021/article/details/49661855

Class Program
{
private static string _connstr;

static void Main (string[] args)
{
_connstr = configurationmanager.connectionstrings["plmconnectionstring"]. 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]. [] If the Where condition has a datetime type, it causes the onchange event to be triggered all the time
using (SqlCommand command = new SqlCommand ("Select Id,machine,jobno from [dbo].[ Plm_issue] ", 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}\t machine:{1}\t jobno:{2}", sdr["id"]. ToString (), sdr["Machine"]. ToString (),

sdr["Jobno"]. ToString ());
}
Sdr. Close ();
}
}
}

private static void Dependency_onchange (object sender, Sqlnotificationeventargs e)
{
SqlDependency dependency = sender as SqlDependency;
Dependency. OnChange-= Dependency_onchange;
if (e.info! = sqlnotificationinfo.invalid)
{
UpdateGrid ()///Repeat registration is required here <span style= "font-family:arial, Helvetica, Sans-serif;" >sqldependency, each registration is executed only once, sqldependency.id can be used to verify registration unique number
}
}
}

Execute the following SQL statement in the database

1. ALTER database name SET new_broker with ROLLBACK IMMEDIATE;
2. ALTER database name SET enable_broker;

C # Real-time monitoring database SqlDependency