C# and SQLDependency: Monitoring Your Database for Data Changes

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:

CREATE TABLE [dbo].[EmployeeList](    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,    [FirstName] [varchar](25) NOT NULL,    [LastName] [varchar](25) NOT NULL,    [PhoneNumber] [varchar](13) NOT NULL) ON [PRIMARY]

We will be using a stored procedure for querying, but this can also be done with a regular text query in your code. Here’s the stored procedure

CREATE PROCEDURE uspGetEmployeeInformationASBEGIN    -- Insert statements for procedure here    SELECT        EmployeeID,        FirstName,        LastName,        PhoneNumber    FROM        dbo.EmployeeListEND

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)

USING [SqlDependencyExample]CREATE QUEUE NewEmployeeAddedQueue;CREATE SERVICE NewEmployeeAddedService ON QUEUE NewEmployeeAddedQueue([http://schemas.microsoft.com/sql/notifications/postquerynotification]); GRANT SUBSCRIBE QUERY NOTIFICATIONS TO SqlDependencyExampleUser;

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:

private bool CheckUserPermissions(){    try    {        SqlClientPermission permissions = new SqlClientPermission(PermissionState.Unrestricted);        //if we cann Demand() it will throw an exception if the current user        //doesnt have the proper permissions        permissions.Demand();        return true;    }    catch    {        return false;    }}

One thing to know, SqlDependency relies on the OnChangeEventHandler Delegate which handles the SqlDependency.OnChange Event, which fires when any notification is received by any of the commands for the SqlDependency object.

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)

/// <summary>/// method for querying our database to get an employee list/// </summary>/// <param name="lview">the ListView we want to display the employee list in</param>private void GetEmployeeList(ListView lview){    //the connection string to your database    string connString = "YourConnectionString";    //the name of our stored procedure    string proc = "uspGetEmployeeInformation";    //first we need to check that the current user has the proper permissions,    //otherwise display the error    if (!CheckUserPermissions())        MessageBox.Show("An error has occurred when checking permissions");    //clear our ListView so the data isnt doubled up    lview.Items.Clear();    //in case we have dependency running we need to go a head and stop it, then    //restart it    SqlDependency.Stop(connString);    SqlDependency.Start(connString);    using (SqlConnection sqlConn = new SqlConnection(connString))    {        using (SqlCommand sqlCmd = new SqlCommand())        {            sqlCmd.Connection = sqlConn;            sqlCmd.Connection.Open();            //tell our command object what to execute            sqlCmd.CommandType = CommandType.StoredProcedure;            sqlCmd.CommandText = proc;            sqlCmd.Notification = null;            SqlDependency dependency = new SqlDependency(sqlCmd);            dependency.OnChange += new OnChangeEventHandler(dependency_OnDataChangedDelegate);            sqlConn.Open();            using (SqlDataReader reader = sqlCmd.ExecuteReader())            {                while (reader.Read())                {                    ListViewItem lv = new ListViewItem();                    lv.Text = reader.GetInt32(0).ToString();                    lv.SubItems.Add(reader.GetString(1));                    lv.SubItems.Add(reader.GetString(2));                    lv.SubItems.Add(reader.GetString(3));                    lview.Items.Add(lv);                }            }        }    }}

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 :)