Use of the SqlDependency class

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