Page 1 of 1

Using SqlDependency To Monitor SQL Database Changes

#1 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Posted 19 February 2010 - 07:08 AM

In this tutorial, I will use the SqlDependency class and Query notifications to monitor SQL Server 2005 database data changes. Query Notifications allow an application to be notified when data has changed in the database.

The purpose of this class is to save you from having to continuously re-query the database to get new data. You would have probably done this by setting up a timer that executes every X amount of seconds so that your display control would be displaying the most up-to-date information. You will no longer have to do this.

We will be using a Service Broker and a QUEUE in SQL Server 2005. These were new additions to SQL Server 2005. I will assume these are also in SQL Server 2008, but can't guarantee.

My example will be doing something very simple. I have a "Users" table in my database with two fields: FirstName and LastName. I am simply displaying these in a ListBox on one form. On a second form, I have textboxes to insert the data into the database.

So first, we need to create the Queue and the Service broker and assign the privileges to the SQL user.

USE YourDatabaseName;

CREATE QUEUE NameChangeQueue;
CREATE SERVICE NameChangeService ON QUEUE NameChangeQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName;

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;


You can now see that we have a new queue and a new service.
Attached Image

Now we move on to the code. The first thing you will need to do is to test if the connecting user has the privileges for the query notifications.

private bool DoesUserHavePermission()
{
    try
    {
        SqlClientPermission clientPermission = new SqlClientPermission(PermissionState.Unrestricted);
        
        // will throw an error if user does not have permissions
        clientPermission.Demand();

        return true;
    }
    catch
    {
        return false;
    }
}



Next, we have our method to get the user names from the database.
private void GetNames()
{
    if (!DoesUserHavePermission())
        return;

    lbNames.Items.Clear();

    //  You must stop the dependency before starting a new one.
    //  You must start the dependency when creating a new one.
    SqlDependency.Stop(connectionString);
    SqlDependency.Start(connectionString);

    using (SqlConnection cn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT FirstName, LastName FROM dbo.[Users]";

            cmd.Notification = null;

            //  creates a new dependency for the SqlCommand
            SqlDependency dep = new SqlDependency(cmd);
            //  creates an event handler for the notification of data
            //      changes in the database.
            //  NOTE: the following code uses the normal .Net capitalization methods, though
            //      the forum software seems to change it to lowercase letters
            dep.onchange += new onchangeEventHandler(dep_onchange);

            cn.Open();

            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    lbNames.Items.Add(dr.GetString(0) + " " + dr.GetString(1));
                }
            }
        }
    }
}



THIS IS VERY IMPORTANT.

1. In the previous code, you will notice that my SQL query does not use the "*" wildcard to return all columns. You MUST return the exact columns that you want. If you use the "*", you will have bad consequences.

2. Also in the previous code, you will notice that my SQL query contains the "two-part" table name. This is also REQUIRED. Using just "TableName" instead of "owner.TableName" will also cause unwanted consequences.


Here is the method for the onchange event
void dep_onchange(object sender, SqlNotificationEventArgs e)
{
    // this event is run asynchronously so you will need to invoke to run on UI thread(if required).
    if (this.InvokeRequired)
        lbNames.BeginInvoke(new MethodInvoker(GetNames));
    else
        GetNames();
    
    // this will remove the event handler since the dependency is only for a single notification
    SqlDependency dep = sender as SqlDependency;

    //  NOTE: the following code uses the normal .Net capitalization methods, though
    //      the forum software seems to change it to lowercase letters
    dep.onchange -= new onchangeEventHandler(dep_onchange);
}



You will also need to stop the dependency when the form closes so that it doesn't leave it running.
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
    SqlDependency.Stop(connectionString);
}



The other events...
private void Form1_Load(object sender, EventArgs e)
{
    GetNames();
}

// button to show other form with textboxes to insert data into database
private void btnShowForm_Click(object sender, EventArgs e)
{
    Form2 f = new Form2();
    f.Show();
}




And my simple second form's code..
private void btnSave_Click(object sender, EventArgs e)
{
    using (SqlConnection cn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO Users VALUES (@FirstName, @LastName)";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
            cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);

            cn.Open();

            cmd.ExecuteNonQuery();
        }
    }
}



And that is really all you have to do. Here are a couple of screenshots.

Before clicking "Save"...
Attached Image

After clicking "Save"...
Attached Image

This will work for SQL Server 2005 databases running on the local PC or on a server. This also works from multiple PCs. You can distribute this little app to multiple people and have them insert names, and you can sit back and watch each one that is entered.

To me, this is one of the best functionalities that I have come across. It is amazing how easy it is to get it running. I plan on starting to use it very soon.

Is This A Good Question/Topic? 0
  • +

Replies To: Using SqlDependency To Monitor SQL Database Changes

#2 Guest_John*


Reputation:

Posted 29 September 2010 - 02:57 AM

This works only for simple applications, as the one you posted. This won't work in scenarios, where changes are made offten to the database, due to SqlDependency limitations.
Was This Post Helpful? 0

#3 Guest_Paul C*


Reputation:

Posted 15 December 2010 - 06:23 AM

This tutorial is exactly what I was after but out of context of an app, I'm not sure how to start applying to connect to DB. I have some experience in C# and have made a few apps that read data from a db and populates a datagridview based on the query the user specifies but getting this code to talk my DB is proving tricky.

This comment also concerns me as I was planning on trying to create an app for a very small company where there can be a few new entries a day, are the SQLDependancy limitations that severe?

Any help or class examples greatly appreciated

Regards, Paul
Was This Post Helpful? 0

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Posted 16 December 2010 - 11:23 AM

I searched Google for these supposed limitations, and I couldn't find any. I am not sure what limitations John was referring to as he neglected to name those limitations.
Was This Post Helpful? 0
  • +
  • -

#5 Xeonen  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 64
  • Joined: 01-January 11

Posted 01 January 2011 - 04:30 AM

I wonder if I can apply the same principles shown in this tutorial to mySQL? Morever, thanks for sharing this information.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1