The Scenario

Imagine an application which generates a queue of data.  And another application that is waiting for a queue item to appear so it can process more data.
How would we know when data had changed?

What are the options?

We could poll the database, hitting the database every couple of minutes to see if something needs to happen?  You can imagine with this option more often than not nothing will have happened, just a wasted call to the database.

Or we could get the database to tell the application some data has changed!

Using SqlDependency in .net and service broker in SQL we can create events which fire when database values change allowing an application to sit and wait for something to happen to a table in SQL.

Let's look at an example

First we need to ensure the broker service is running on our database, default is disabled

 This returns a list of databases and their current state (0-Disabled / 1-Enabled)

 Then, to enable the Service Broker on the database, issue the following command...

 Using the "ProductBuild" database we'll create a simple table to hold a queue of information.

Now we need to write some code. To keep this example simple we're just using a console application to view the results.  We're using the SqlDependency object, this allows us to receive notifications from the event handler.

First we "Start" the listener, using our connection string.

Then we connect to our table. The "CommandText" must expand all database columns, alternatively you could connect to a stored procedure.

Now we create the dependency and the event we wish to fire.

Finally we can act on the event. The event will only fire once, so, to overcome this, we unhook the event and recreate it.

We now have a listener (c# console application), and a simple table held in SQL. However if we add a row to our monitored table, we find that an event is triggered and our console application will notify us of what's happened immediately.

If data changes in the specified tables (Update / Insert / Delete) events will be fired.

So, what do we think?

For any application that needs to show data as it's changing, the SQLDependency object is definitely worth considering. It's efficient yet effective and is relatively easy to implement.  Rather than making unnecessary calls polling a database we can wait for the database to notify the application of data changes. Job done.