Using SqlDependency results in constant updates

Question

I pulled an example from this MSDN page and have used it pretty much verbatim. When run the code compiles properly but changeCount increments endlessly whether or not there has actually been a change to the data returned. When a change actually has occurred dataGridView1 reflects the change correctly. Why does my SqlDependency seem like it's firing in a loop even though there apparently have been no changes?

Here's the source:

#region Using directives
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security.Permissions;
using System.Text;
using System.Windows.Forms;
#endregion

namespace PreAllocation_Check
{
    public partial class Form1 : Form
    {
        int           changeCount = 0;
        const string  tableName = "MoxyPosition";
        const string  statusMessage = "Last: {0} - {1} changes.";
        DataSet       dataToWatch = null;
        SqlConnection MoxyConn = null;
        SqlCommand    SQLComm = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            if (CanRequestNotifications())
            {
                SqlDependency.Start(GetConnectionString());

                if (MoxyConn == null)
                    MoxyConn = new SqlConnection(GetConnectionString());

                if (SQLComm == null)
                {
                    SQLComm = new SqlCommand(GetSQL(), MoxyConn);

                    SqlParameter prm = new SqlParameter("@Quantity", SqlDbType.Int);
                    prm.Direction = ParameterDirection.Input;
                    prm.DbType = DbType.Int32;
                    prm.Value = 100;
                    SQLComm.Parameters.Add(prm);
                }

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

                GetData();
            }
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            SqlDependency.Stop(GetConnectionString());
            if (MoxyConn != null)
                MoxyConn.Close();
        }

        private bool CanRequestNotifications()
        {
            try
            {
                SqlClientPermission SQLPerm = new SqlClientPermission(PermissionState.Unrestricted);
                SQLPerm.Demand();
                return true;
            }
            catch
            {
                return false;
            }
        }

        private string GetConnectionString()
        {
            return "server=***;database=***;user id=***;password=***";
        }

        private void GetData()
        {
            dataToWatch.Clear();
            SQLComm.Notification = null;
            SqlDependency SQLDep = new SqlDependency(SQLComm);
            SQLDep.OnChange += new OnChangeEventHandler(SQLDep_OnChange);

            using (SqlDataAdapter adapter = new SqlDataAdapter(SQLComm))
            {
                adapter.Fill(dataToWatch, tableName);
                dataGridView1.DataSource = dataToWatch;
                dataGridView1.DataMember = tableName;
            }
        }

        private string GetSQL()
        {
            return "SELECT PortID, CONVERT(money, SUM(PreAllocPos), 1) AS PreAllocation, CONVERT(money, SUM(AllocPos), 1) AS Allocation, CONVERT(money, SUM(PreAllocPos) - SUM(AllocPos), 1) AS PreLessAlloc " +
                   "FROM MoxyPosition " +
                   "WHERE CONVERT(money, PreAllocPos, 1) <> CONVERT(money, AllocPos, 1) " +
                   "GROUP BY PortID " +
                   "ORDER BY PortID ASC;";
        }

        void SQLDep_OnChange(object sender, SqlNotificationEventArgs e)
        {
            ISynchronizeInvoke i = (ISynchronizeInvoke)this;

            if (i.InvokeRequired)
            {
                OnChangeEventHandler tempDelegate = new OnChangeEventHandler(SQLDep_OnChange);
                object[] args = { sender, e };
                i.BeginInvoke(tempDelegate, args);
                return;
            }

            SqlDependency SQLDep = (SqlDependency)sender;
            SQLDep.OnChange -= SQLDep_OnChange;

            changeCount++;
            DateTime LastRefresh = System.DateTime.Now;
            label1.Text = String.Format(statusMessage, LastRefresh.TimeOfDay, changeCount);

            GetData();
        }
    }
}

Edit: It's worth noting that the database I want to run this against does not currently have the Broker Service enabled, and so to test my code I backed up my target database and restored it with a new name, then ran ALTER DATABASE my_db_name SET ENABLE_BROKER against it. All of my testing has been on this alternate database, which means I'm the only user on it.

Answer 1

This is an old question, but the problem is that your query doesn't meet the requirements.

Short answer:
add schema name to the table "FROM DBO.MoxyPosition " +

Longer answer:
You can see a list of requirements here, which are very similar to those of creating an indexed view. When a SQL Dependency is registered, if it is invalid the notification immediately fires letting you know it's invalid. When you think about it, this makes sense, because how can Visual studio know what the internal requirements are for the SQL Engine?

So in your SQLDep_OnChange function you'll want to look at the reason the dependency fired. The reason is in the e variable (info, source, and type). Details on the event object can be found here:

• Info options
• Source
• Type

For your specific case notice how MS describes the Type property :

Gets a value that indicates whether this notification is generated 
because of an actual change, OR BY THE SUBSCRIPTION.