8 October 2015

Monitoring SQL data changes with SQLDependency

Sometimes it’s necessary monitoring data changes and process immediate actions or notifications. In this article you can see how it can be done using the SQL Server and SqlDependency.

In this case i use our clinical management software the Easy Clinic for the example. Imagine the necessity for the patient to be notified by SMS after his exam cancelation.

In our case, it’s easy! Being our software this can be done by changing the cancelation method to invoke the send SMS. But if it isn’t? Imagine that the Easy Clinic are property of other company and the only thing that we have access is the database without chance for reengineering the source code.

“Ok! Let’s implement a solution that checks the exam table between 5 and 5 seconds to monitoring data changes and send the notification.”

This can be a solution, but do the math! This method will run 17.280 times a day, therefore, each call will be a connection to the database only to check a value. And if there is no exam cancelations, this run over an over for nothing? And can be the database notifying my application when it changes? Yes it’s possible with SQL Server Service Broker and SQL Dependency.

“The SqlDependency object represents a query notification dependency between an application and an instance of SQL Server. An application can create a SqlDependency object and register to receive notifications via the OnChangeEventHandler event handler.”

First of all we need to check if the Service Broker is activated on database.

SELECT NAME, IS_BROKER_ENABLED FROM SYS.DATABASES
WHERE NAME = ‘EasyClinicServer’

To get the Service Broker active run the follow script (The Service Broker is available since SQL Server 2005)

ALTER DATABASE EasyClinicServer SET ENABLE_BROKER

This is our scheduling table structure.

And there is a few rows for our example:

Now on the C# side, we will implement the SQLDependency.

The SQLDependency is a class from the namespace System.Data.SqlClient that you must reference on your project.

To initialize the SQLDependency on the connection you must do:

SqlDependency.Stop(this.sConnectionString);
SqlDependency.Start(this.sConnectionString);

The first Stop ensure that there is no dependency running.

(If the Service Broker isn’t activated on the database when you call the method Start will cause an invalid operation exception.)

Now to get the table changes is necessary subscribe the query that will responsible for listening the table changes. The next query only get rows for the type 1 (“Exams”) and when the status is “C” (Canceled). Every time that the query result changes the Service Broker automatically notify my application over the SQLDependency.

string sQuery = “SELECT [CodEvento],[CodPaciente],[CodTipoEvento], [Estado] FROM [dbo].[tblEventoAgenda] WHERE [CodTipoEvento] = 1 AND [Estado] = ‘C'”;

Let’s configure the instance using the next method.

private async void InstanceDependency()

{

this.sqlConnection = new SqlConnection(this.sConnectionString);
this.sqlCommand = new SqlCommand(sQuery, sqlConnection);
this.sqlCommand.CommandType = CommandType.Text;
this.sqlDependency = new SqlDependency(this.sqlCommand);
this.sqlDependency.OnChange += this.SqlDependencyOnChange;
await this.sqlCommand.Connection.OpenAsync();
await this.sqlCommand.ExecuteReaderAsync(CommandBehavior.CloseConnection);

}

The next method will handle the event OnChange. Every time that the service broker notifies my application this method fires the actions implemented in there. For example, invoke the send SMS method.

private void SqlDependencyOnChange(object sender, SqlNotificationEventArgs eventArgs)

{

if (eventArgs.Info == SqlNotificationInfo.Invalid)

{

Console.WriteLine(“The query is not valid.”);

return;
}

else

{

Console.WriteLine(“Your data was changed! Let’s send an SMS!”);

SendSms();

}

this.InstanceDependency();

}

The SQLDependency only notifies when data changes, but don’t return the data changed. To do that you must complete your application with other methods.

This can be a useful option for who need implement solutions of that type, however you must consider some points, like:

The statement must not reference tables or views from other databases or servers.
The statement must not reference system tables or views, including catalog views and dynamic management views.
The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.

References

https://msdn.microsoft.com/library/ms181122.aspx

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency(v=vs.110).aspx

.

.

.

.

     Carlos Sereno
          Manager
Solutions Development