Monitor event notifications

There might be times when you’d wish to receive a notification about a change in the returned results of a command. In these times, you would use the SqlDependency class, living in System.Data.SqlClient. For it to work, you should enable SQL Service Broker for your database, because raising notifications requires you to do so.

As I stated, SqlDependency can be used to query whether or not a given SqlCommand’s result changed (or any related changes have occurred in the database, such as a failure). There are two ways to get notified about changes: query the HasChanges property of the current SqlDependency instance, which is a Boolean value informing you about changes in the database. The other, more robust approach is to use the OnChange event of it. It returns an instance of SqlNotificationEventArgs, which exposes the following properties:

  • Info: gets the reason of the notification.
  • Source: gets the source of the notification.
  • Type: the SqlNotificationType of the notification. Can be Change, Subscribe or Unknown.

Now that you know the basics, here’s an example:

SqlDependency theDependency = new SqlDependency(theCommand);
using(theConnection)
{
theConnection.Open();
theDependency.OnChange += new OnChangeEventHandler(theDependency_OnChange);
SqlDependency.Start(theConnection.ConnectionString);
SqlDataReader theReader = theCommand.ExecuteReader();
}

Then you can define a method to work with the notification, as follows:

void theDependency_OnChange(object sender, SqlNotificationEventArgs e)
{
Console.WriteLine(e.Source);
}

You could noticed that we called the static Start method of the SqlDependency class. This is a requirement if you haven’t specified any string options in the SqlDependency constructor. You can turn off notifications by calling the static Stop method of SqlDependency.

Further Readings

Detecting Changes with SqlDependency

Advertisements

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: