Introduction :
SqlDependency provides such a capability. : When the data in the monitored database is changed , SqlDependency will automatically trigger OnChange event to notify the application , so that the system can automatically update the data ( or cache ) the purpose.
Application Scenarios:
When the data in the database changes, need to update the cache, or need to update the relevant business data, or send a message or a text message and so on, if the database is SQL Server , you might consider using SqlDependency Monitor the data changes of a table in the database and set out the corresponding events.
Application Prerequisites :
when the application starts, start SqlDependency for SQL Server monitoring of the connection.
at the end of the application, stop SqlDependency for SQL Server monitoring of the connection.
Web Application:
protected void Application_Start ()
{
System.Data.SqlClient. SqlDependency . Start (configurationmanager. connectionstrings["Framework_sqlserver"]. ConnectionString);
}
protected void application_end (object sender, EventArgs e)
{
System.Data.SqlClient. SqlDependency . Stop (configurationmanager. connectionstrings["Framework_sqlserver"]. ConnectionString);
}
WINDOWS Application:
Static void Main (string[] args)
{
_connstr = ConfigurationManager . connectionstrings["Framework_sqlserver"]. ToString ();
SqlDependency . Start (_CONNSTR); // incoming connection string to initiate database-based snooping
...
SqlDependency . Stop (_CONNSTR); // incoming connection string to initiate database-based snooping
}
Specific application:
// when the data in the table is changed, go Onchangeeventhandler Events
Private Static void UpdateGrid ()
{
using (SqlConnection connection = newSqlConnection(_CONNSTR))
{
// dependency is based on a table of and query statements can only be simple query statements no or *, You must also specify the owner , [dbo ". []
using (SqlCommand command = newSqlCommand("Select Id,taskname, Completionnumber,uploadfilepath,querycount,starttime,endtime,taskstatus,downloadfilepath,creater,createdate from [dbo]. [Res_batchquery] WHERE ID = ", Connection))
{
Command.commandtype = commandtype. Text;
Connection. Open ();
SqlDependency dependency = New SqlDependency (command);
dependency. OnChange + = newonchangeeventhandler(dependency_onchange);
SqlDataReader SDR = command. ExecuteReader ();
Console . WriteLine ();
while (SDR). Read ())
{
Console . WriteLine ("id:{0}\\taskname:{1}\\completionnumber:{2}", sdr["Id"]. ToString (), sdr["TaskName"]. ToString (), sdr["Completionnumber"]. ToString ());
}
Sdr. Close ();
}
}
}
// Specific Events
Private Static void Dependency_onchange (object sender, Sqlnotificationeventargs e)
{
if (E.type = = sqlnotificationtype. Change)// data is only re - acquired and data is changed
{
UpdateGrid ();
}
}
Things to keep in mind:
1 , the application must start or stop at the beginning or end of the SQL Server the monitoring.
2 , only SQL The fields that need to be queried in the statement are not monitored, and no data is changed when no query is triggered . Dependency_onchange event.
3 , query statements can only be simple query statements , can't bring Top or *, You must also specify the owner , that is similar [dbo]. [].
4 , WHERE the data in the condition cannot be too complex, or it may not be monitored.
5 , the data for the fields to be queried cannot be too complex. When testing, there is a field that holds data in Json format. If this field is also written to an SQL statement, it will not be monitored.
SQL Server related configuration required:
// to set a rollback for a database agent
ALTER DATABASE [DBName] SET new_broker with ROLLBACK IMMEDIATE;
// set up a proxy for a database
ALTER DATABASE [DBName] SET enable_broker;
// To query whether a database has started a proxy
SELECT is_broker_enabled from sys.databases WHERE name = ' [DBName] '
is_broker_enabled to be 0 indicates that the agent is not started
is_broker_enabled to be 1 indicates that the agent has been started
Use of the SqlDependency class