How to organize the notification of the program that certain data in the database have changed? It is desirable that in the event of a change in the data in the database, a stored procedure is launched that returns data to the program. Are there any standard classes for this? Manually do not want to interview.

  • 2
    Look at this msdn.microsoft.com/ru-ru/library/62xk7953 ( v=vs.110). Aspx - matrix
  • matrix, interesting, but it will still do SELECT exactly on a timer and watch? Is this purely for convenience? Or some new SQL functionality is involved, just did not hear about it before. - xSx
  • 2
    @xSx, will use the DBMS internal event mechanism here. The functionality is not new, it appeared in SQL Server 2005. - matrix

1 answer 1

You can use the SqlDependency class.

1) Ensure that Service Broker is enabled for the target database.

SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database_name';

2) If Service Broker is not enabled, then you need to enable it. There should be no active connections from the database.

 ALTER DATABASE [Database_name] SET ENABLE_BROKER;

3) Example C # applications with MSDN

 void Initialization() { // Create a dependency connection. SqlDependency.Start(connectionString, queueName); } void SomeMethod() { // Assume connection is an open SqlConnection. // Create a new SqlCommand object. using (SqlCommand command=new SqlCommand( "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", connection)) { // Create a dependency and associate it with the SqlCommand. SqlDependency dependency=new SqlDependency(command); // Maintain the refence in a class member. // Subscribe to the SqlDependency event. dependency.OnChange+=new OnChangeEventHandler(OnDependencyChange); // Execute the command. using (SqlDataReader reader = command.ExecuteReader()) { // Process the DataReader. } } } // Handler method void OnDependencyChange(object sender, SqlNotificationEventArgs e ) { // Handle the event (for example, invalidate this cache entry). } void Termination() { // Release the dependency. SqlDependency.Stop(connectionString, queueName); }

In the SqlCommand object, after the select keyword, the query lists the fields of the database tables whose changes in values ​​should be monitored. It is also important that the scheme be explicitly specified ( dbo by default).

When an event occurs, the OnDependencyChange method is OnDependencyChange .