Use of the SqlDependency class

Source: Internet
Author: User
Tags connectionstrings

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.