MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

Using the SqlDependency Class is a good way to make your data driven application (whether it be Web or Windows Forms) more efficient by removing the need to constantly re-query your database checking for data changes.

For the purposes of this article we'll be discussing using SqlDependencywith SQL 2005, but I'm sure it works the same, or very similar with SQL 2008. Using SqlDependency to monitor for data changed relies on two things from SQL Server, Service Broker. A Service Broker allows the sending of asynchronous messages to and from your database.

The second item it relies on Queues, your Service Broker will use your Queue to create loose coupling between the sender and receiver. The sender can send its message to the Queue and move on, and relies on the Service Broker to make sure whatever message was put into the Queue is processed and a response is sent back to the sender (your application).

For this article I’m using a sample database I created named SqlDependencyExample and a table named Employee whick has this structure:

NOTE: Notice in the stored procedure we have dbo in the table name, when using dependency to monitor data changes you have to have the table in the format [owner].[Tablename] of it can cause unwanted results, so to avoid that just use the said format.

First thing we need to do is create your Service Broker and Queue so we can send messages back & forth between our database. In my example I have a simple table that holds employee names & phone numbers. This is how we create the Service Broker & Queue (My database name is SqlDependencyExample but change that with your database name (We also need to give our SQL user permission to access it)

Now we move on to the fun part, the code (I know that's what you're waiting for). Before this can work we need to check and make sure the connected user has the proper permissions for the notifications. We can do this with creating a simple method CheckUserPermissions which uses the SqlClientPermissions Class to check the permissions of the currently connected user. So here’s the simple method for accomplishing this:

Now for getting the employee list, in this method we will query our EmployeeList table to get the employees information. We will also set the OnChange event of our SqlDependency object so that it can let us know when data has changed in our table and re-populate it with the latest employee list. Before trying to access our database we will call the method CheckUserPermissions method we created earlier to make sure the current user has the proper permissions, if not we display a message otherwise we move on to getting the employee list and populating a ListView with the ID, first & last name of the employee and their phone number.

Here’s the GetEmployeeList method, which expects a parameter of type ListView (which is what will be displaying our employee list)

Notice we set the OnChangeEventHandler of our SqlDependency object to dependency_OnDataChangedDelegate, this is the method that will re-query our table and send notifications when the data has changed in our EmployeeList table. In this method we invoke the work onto the main UI thread, this will help us avoid the dreaded cross—thread exception when we go to re-populate the ListView control when any notifications are sent to our application.

Since our method (GetEmployeeList) required a parameter we cannot use the standard MethodInvoker delegate (as this cannot accept parameters). So what we will do is create our own Delegate which can accept our parameter. Here’s the delegate (very simple):

private delegate void getEmployeeListDelegate(ListView lv);

Our dependency_OnDataChangedDelegate requires a SqlNotificationEventArgs in the signature. Here we check the control being used to make sure InvokeRequired is true, if no then we use Invoke to invoke the work onto the main UI thread for re-populating our ListView, otherwise we just call our method to re-query:

private void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e){ //avoid a cross-thread exception (since this will run asynchronously) //we will invoke it onto the main UI thread if (listView1.InvokeRequired) listView1.Invoke(new getEmployeeListDelegate(GetEmployeeList), listView1); else GetEmployeeList(listView1); //this example is for only a single notification so now we remove the //event handler from the dependency object SqlDependency dependency = sender as SqlDependency; dependency.OnChange -= new OnChangeEventHandler(dependency_OnDataChangedDelegate);}

NOTE: One thing to remember is you have to stop the dependency from querying your database when your form is closed, to do this use the forms FormClosing event to stop the work in the form you are using the dependency work in.

That’s how you use the SqlDependency Class for monitoring data changes in your database without having to use something like a timer control to re-query at certain intervals. Thanks for reading and happy coding :)

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.