Notify winforms application by Sql Server database changes

Question

I created a Sql server database, in which I added a table named user. Then I executed this script

ALTER DATABASE [TestNotification] SET ENABLE_BROKER

I'd like to use SqlDependency class to notify a winforms application when the user table were changed.

 namespace Watcher
{
    public partial class Form1 : Form
    {
        private int changeCount = 0; 
        private const string statusMessage = "{0} changes have occurred."; 
        private DataSet dataToWatch = null;
        private SqlConnection connection = null;
        private SqlCommand command = null;

        public Form1()
        {
            InitializeComponent();
            button1.Enabled = CanRequestNotifications();
            this.FormClosed += Form1_FormClosed;
        }

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

        private bool CanRequestNotifications()
        {
            // In order to use the callback feature of the
            // SqlDependency, the application must have
            // the SqlClientPermission permission.
            try
            {
                SqlClientPermission perm =
                    new SqlClientPermission(
                    PermissionState.Unrestricted);

                perm.Demand();

                return true;
            }
            catch
            {
                return false;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {

            changeCount = 0;
            label1.Text = String.Format(statusMessage, changeCount);
            //SqlDependency.Stop(GetConnectionString());
            SqlDependency.Start(GetConnectionString());

            if (connection == null)
            {
                connection = new SqlConnection(GetConnectionString());
            }

            if (command == null)
            {
                command = new SqlCommand(GetSQL(), connection);
            }
            if (dataToWatch == null)
            {
                dataToWatch = new DataSet();
            }

            GetData();

        }

        private string GetConnectionString()
        {
            return @"Data Source=BILOG-PRT-12\SQLEXPRESS; Initial Catalog=TestNotification;Integrated Security=True";
        }

        private string GetSQL()
        {
            return "Select   [id],[nom],[prenom],[age]  from   [dbo].[user]";
        }


        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {

            MessageBox.Show("modification Occurred");
            ISynchronizeInvoke i = (ISynchronizeInvoke)this;
            if (i.InvokeRequired)
            {
                OnChangeEventHandler tempDelegate =new OnChangeEventHandler(dependency_OnChange);
                object[] args = { sender, e };
                i.BeginInvoke(tempDelegate, args);
                return;
            }

            SqlDependency dependency = (SqlDependency)sender;
            dependency.OnChange -= dependency_OnChange;
            ++changeCount;
            label1.Text = String.Format(statusMessage, changeCount);
             GetData();

        }
        private void GetData()
        {

            //dataToWatch.Clear();
            //command.Notification = null;
            SqlDependency dependency = new SqlDependency(command);
            if (connection.State != ConnectionState.Open) connection.Open();
            using (var dr = command.ExecuteReader())
            {
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
            }

        }



    }
}

All the broker service are running :

I launched the application, Then I clicked into the button and finally I go the Sql Server management studio And I inserted a new row. The problem is that the message box in the application is not shown so the c# application is not notified by SQL Server!!!

So I need to know :

1. Why this happens?
2. Which step I forget ?
3. How can I resolve this issue?

Answer 1

There's quite a few limitations with SqlDependency. To quote one relevant issue:

The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names.Notice that this means that all tables referenced in the statement must be in the same database.

(see https://msdn.microsoft.com/library/ms181122.aspx for the full list)

You have to explicitly use two-part name (e.g. dbo.user instead of just user).

Also, you need to execute the command. It doesn't just start working automagically :) Adding a simple using (var dr = command.ExecuteReader()) {} should be enough.