Query Notifications & SqlDependency

Ran into some problems while working on a research spike on Query Notifications and thought to note it down for future reference. If you need the database to notify you of updated data so that you can perform some tasks either to update a cache or to fire-off some processes, then Query Notifications maybe one solution for you (instead of polling).

In a nutshell, it is not really a product but merely a solution that rides on SQL Server Service Broker and .NET's SqlDependency class. I will go through a very rough sample to get you started.

With references to here, we will need to enable service broker on our database (in this example it is LeaveSample). We can achieve that by setting the property in SQL Server Management Studio or executing the following command:

ALTER DATABASE LeaveSample SET ENABLE_BROKER

Next, we will need to create a Queue and a Service. We will need to use SQL as there are no UI for this as yet.

CREATE QUEUE LeaveMessages;

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

We are done with the SQL part. Let's move on to the code. I will use a very rough (and I mean very rough) Windows Form application for the client. The application has nothing but only a DataGridView dragged onto it and here's the code-behind of the Form1.cs. You can also refer here for a guide.

private void Form1_Load(object sender, EventArgs e)

{

    // Start listening.

    SqlDependency.Start(CONNECTION_STRING);

    Register();

}

private void Register()

{

    // Create Connection.

    using (var cn = new SqlConnection(CONNECTION_STRING))

    {

        // Create command.

        using (var cmd = new SqlCommand("SELECT LeaveID, StartDate, EndDate, Description FROM dbo.Leaves", cn))

        {

            // Initialize SqlDependency and attach it to the command.

            var dependency = new SqlDependency(cmd);

            dependency.OnChange += dependency_OnChange;

            // Populate grid view on another thread.

            this.Invoke((MethodInvoker)delegate

            {

                // Create data adapter.

                using (var da = new SqlDataAdapter(cmd))

                {

                    DataTable dt = new DataTable();

                    // Populate table.

                    cn.Open();

                    da.Fill(dt);

                    cn.Close();

                    // Bind to grid view.

                    dataGridView1.DataSource = dt;

                };

            });

        }

    }

}

void dependency_OnChange(object sender, SqlNotificationEventArgs e)

{

    // Displays what type of notification. Should be 'Change'.

    Debug.WriteLine(e.Type.ToString());

    // One time event. Re-registering required.

    Register();

}

private void Form1_FormClosing(object sender, FormClosingEventArgs e)

{

    // Stop listening.

    SqlDependency.Stop(CONNECTION_STRING);

}

I'm using the sa account in my connection string (not shown here). If you are using integrated security, you may need to manually assign permissions as mentioned in here.

In both the Form Load and FormClosing event, I set the SqlDependency static methods to Start and Stop listening respectively. The Register method is called in the Load event where an instance of the SqlDependency is created and attached to the SqlCommand.

You will notice that the OnChange event will call Register again. This is because Service Broker behaves like a queue and once a message has been dequeued, the event will no longer trigger the next time. (This is somewhat similar to MSMQ's asynchronous API behavior).

Now with all these in place, if I update the data in my Leaves table i.e. Insert, Update or Delete, my Windows Form application will automatically refresh the data in the DataGridView.

Warning! Take serious note when writing your SQL queries. Query Notifications have some pretty rigid rules to follow which you can read here. Please go through them thoroughly as my initial mistake was not prefixing my table with dbo. and the code went into an endless subscription loop :(

Hugs and Happy Coding!

P.S. I would like to thank my associate for stirring up my interest to complete this research spike.