Service Broker does not notify about changes taking place between unsuscribing and re-wiring to the SqlDependency.OnChange event

Question

I have this code:

    public partial class Form1 : Form
    {
        private string _connectionString = @"Data Source=(local)\SQLEXPRESS; Initial Catalog=MyDatabase; User Id=sa; Password=test";
        //private SqlCommand _command;
        //private SqlConnection _connection;
        public Form1()
        {
            InitializeComponent();
        }

        public void Notify(Object sender, SqlNotificationEventArgs args)
        {
            var obj = sender as SqlDependency;
            obj.OnChange -= Notify;

            Listen();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlDependency.Start(_connectionString,"CacheQueue");
            Listen();
        }

        private void Listen()
        {
            using (var _connection = new SqlConnection(_connectionString))
            {
                _connection.Open();

                using (SqlCommand command = new SqlCommand(
                    @"SELECT [C1] ,[C2],[C3] FROM [dbo].[Table]",
                    _connection))
                {

                    SqlDependency dependency = new SqlDependency(command, "Service=CacheService", 0);

                    dependency.OnChange += new OnChangeEventHandler(Notify);

                    command.ExecuteNonQuery();
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SqlDependency.Stop(_connectionString, "CacheQueue");
        }
    }

I am using SQL Server 2008 Express Edition and I am testing on my local machine. In SQL Server i executed the following statements:

ALTER DATABASE MyDatabase SET ENABLE_BROKER;

CREATE QUEUE CacheQueue;

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

If I execute multiple updates on table "Table" between receiving the first notification and re-wiring to the OnChange event I don't receive any other notifications.

For example, let's say that I run this statement in SQL Server:

UPDATE [Table] 
SET C1 = 'aaaa'
WHERE Id = 7 -- an existing Id

After that, I receive the notification and the "Notify" event handler is fired. But if I stay with a breakpoint in it and I go back to SQL and I execute other DML statements on the same table, I don't get any notifications. My expectation was that after re-wiring to OnChange event I would be able to get all changes that happened in the meantime. Aren't they kept in a queue in Service Broker? How can I achieve this behavior?

Answer 1

This has actually nothing to do with Service Broker. It is all about Query Notifications, an unrelated feature that uses Service Broker to deliver its notifications. Please read The Mysterious Notification to understand what we're talking about.

You must understand what Query Notifications do: it allows a client application to be notified when the result of a query changes. It is, very specifically, not a change tracking mechanism. For change tracking use Change Tracking or Change Data Capture.

As such, your expectations are unfounded. You should expect to be notified, once, after you submit a query. That's all there is.