SqlDependency OnChange Not Firing

Question

This is the first time I've ever needed to use an SqlDependency so I am hoping that its a stupid mistake I've made.

The problem I'm having is that the OnChanged event doesn't fire when the sql table changes. No errors or anything just it doesn't fire.

Here is the code

public class SqlWatcher
{
    private const string SqlConnectionString = "Data Source = CN-PC08\\DEV; Initial Catalog=DEP; User = sa; Password=******";

    public SqlWatcher()
    {
        SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
        perm.Demand();

        SqlCommand cmd = new SqlCommand("SELECT [DataAvaliable], [RowNumber] FROM [dbo].[Trigger]", new SqlConnection(SqlConnectionString));
        SqlDependency sqlDependency = new SqlDependency(cmd);
        sqlDependency.OnChange += On_SqlBitChanged;
    }

    private void On_SqlBitChanged(object sender, SqlNotificationEventArgs sqlNotificationEventArgs)
    {
        SqlDependency dependency = (SqlDependency)sender;
        dependency.OnChange -= On_SqlBitChanged;

        // Fire the event
        if (NewMessage != null)
        {
            NewMessage(this, new EventArgs());
        }
    }

    public void Start()
    {
        SqlDependency.Start(SqlConnectionString);
    }

    public void Stop()
    {
        SqlDependency.Stop(SqlConnectionString);
    }

    public event EventHandler NewMessage;

And in my main window I have this

public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();

        try
        {
            SqlWatcher sqlWatcher = new SqlWatcher();
            sqlWatcher.Start();
            sqlWatcher.NewMessage += On_NewMessage;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

    private void On_NewMessage(object sender, EventArgs eventArgs)
    {
        MessageBox.Show("Message Received");
    }
}

So the expected behaviour is that if I run the following sqlQuery a messageBox will be displayed saying "Message Received"

INSERT INTO [DEP].[dbo].[Trigger] Values(0,3)

Could anyone give me a hint on what to check/change?

I'm aware that only a subset of Sql features can be used in dependencies but I don't think I'm trying to do anything to fancy here.

Solution

I'm hoping that its a stupid mistake I've made.

Unfortunately (or fortunately?) you are making several mistakes.

1. First is you need to understand that Query Notifications will invalidate one query. So you will only be notified at most once and you have to re-subscribe again (re-submit the query) if you want to receive further notifications.

2. Next you need to understand that you will be notified for any reason, not only for changes. In your callback you must check the reason you're notified, which are passed in via the SqlNotificationEventArgs.

3. Next you need to understand asynchronous programming basic principles: if you subscribe for an event make sure you subscribe before the event can happen first time. Case in point: the On_SqlBitChanged can fire as soon as you submit the query. This should happen in the SqlWatcher.SqlWatcher constructor, but you subscribe to the sqlWatcher.NewMessage after the constructor runs. On_SqlBitChanged can be invoked between the constructor finishes before you hook up the NewMessage event callback in which case the notification is silently ignored.

4. If you want to use a service make sure you start it before you use it. You are using SqlDependency in SqlWatcher.SqlWatcher but you start it after that when you call SqlWatcher.Start().

5. Finally, if you want to be notified of changes on a query you have to submit the query. You are constructing the SqlCommand object, set up the notification and then... discard the object. Unless you actually submit the query, you did not yet subscribed to anything.

Suggestions for fix:

• Make Start and Stop statics, call Start in application start up.
• Make sure you subscribe to NewMessage before you submit the query
• Actually submit the query (call SqlComamnd.ExecuteQuery())
• Inspect the Info, Type and Source in the On_SqlBitChanged callback, if your submission contains an error this is the only way to learn (the SqlComamnd.ExecuteQuery() will succeed even if the notification request is invalid)
• You must re-subscribe once you're notified of a change, execute the query again.

One more thing: don't invoke UI code in background callbacks. You cannot call MessageBox.Show("Message Received"); from a callback, you must route through the form main thread via Form.Invoke. YEs, I know that strictly speaking MessageBox.Show does work on a non-UI thread but you will soon move away from alert boxes to actually form interaction and then things will break.