Data change notification using Service Broker

A quick demo of using query notification to enable application to present data change:

Let’s start with a test database:

–Setup Test base

create database MyTestDB

go

use MyTestDB

go

create table Test

(Number int)

go

insert into Test

values (1)

go

select * from Test

–Enable Service Broker

use MytestDB

create master key encryption by password = ‘Password1!’

go

alter database MyTestDB set ENABLE_BROKER with ROLLBACK IMMEDIATE

go

–Change database owner since it was created under my windows ID

sp_changedbowner ‘sa’

go

Here we enable Service Broker and Query Notification to all application to subscribe data changes. Now we need to create an application to be able to see the changes.

The application we are going to build has 2 controls: a lable (lblText) and a button (btnStart).

lblText.Text is set to 0 to start with, here is the source code:

using System;

using System.ComponentModel;

using System.Data;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace QueryNotification

{

    public partial class Form1 : Form

    {

        const string SQL_COMMAND = “SELECT NUMBER FROM TEST”;

        const string CONNECT_STRING = “Data Source=localhost;Initial Catalog=MyTestDB;Integrated Security=SSPI;”;

        private SqlConnection con;

        private SqlCommand cmd;

        private DataSet data;

        public Form1()

        {

            InitializeComponent();

        }

        private void btnStart_Click(object sender, EventArgs e)

        {

            SqlDependency.Stop(CONNECT_STRING);

            SqlDependency.Start(CONNECT_STRING);

            if (con == null) con = new SqlConnection(CONNECT_STRING);

            if (cmd == null) cmd = new SqlCommand(SQL_COMMAND,con);

            if (data == null) data = new DataSet();

            GetData();

        }

        private void GetData()

        {

             data.Clear();

            cmd.Notification = null;

            SqlDependency dependency = new SqlDependency(cmd);

            dependency.OnChange += new OnChangeEventHandler(number_OnChange);

            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))

            {

                adapter.Fill(data);

                lblText.Text = data.Tables[0].Rows[0][0].ToString();

            }

        }

        private void number_OnChange(object sender, SqlNotificationEventArgs e)

        {

            ISynchronizeInvoke i = (ISynchronizeInvoke)this;

            if (i.InvokeRequired)

            {

                OnChangeEventHandler deleg = new OnChangeEventHandler(number_OnChange);

                object[] args= {sender, e};

                i.BeginInvoke(deleg,args);

                return;

            }

            SqlDependency dependency = (SqlDependency)sender;

            dependency.OnChange -= number_OnChange;

            GetData();

        }

    }

}

When we run this code, a Windows Form application shows like this:


Let’s click the Start button:


lblText changed to 1, which is the current value in database. What happens if we update the database:

–Update data

update Test

set Number = 2

lblText changes to 2 almost immediately:


Great, without too much of coding, we have successfully setup a data change presenting process with minimum resource!

Note: Query notification does have a performance impact on server side and should be used for less frequently changed data. Microsoft recommends ten concurrent subscribers.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s