SQL Dependency in C#

Question

I'm trying to figure out how to use SQL Dependency (C# 4.0) to 'listen' for changes to a database. I've seen quite a few things on the web, but they seem to be tailored (naturally) for using the dependency to pull the same data that the SQL Dependency is dependent on. For example, this article.

What I'm trying to do is create a dependency that, when triggered, results in a number of different SQL 'Select' queries (which I can store in other methods etc). For example: I'm trying to set a dependency that watches the number of rows in table. When the number of rows increases, then do x, y, z (ie my program doesn't care what the number of rows is, just that it's increased, and when it does do a bunch of things).

Any thoughts on what would the best way to do this?

EDIT: I've attached my code as I have it currently. I'm trying to figure out how to separate setting up the SqlDependency from the GetData() process. Currently though, I think I go into a bit of an endless loop as after I remove the event handler and rerun "SetupSqlDependency()", it goes right back into the event handler

    private void SetupSQLDependency()
    {
        // Tutorial for this found at:
        // http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/

        SqlDependency.Stop(connectionString);
        SqlDependency.Start(connectionString);

        sqlCmd.Notification = null;

        // create new dependency for SqlCommand
        SqlDependency sqlDep = new SqlDependency(sqlCmd);
        sqlDep.OnChange += new OnChangeEventHandler(sqlDep_OnChange);

        SqlDataReader reader = sqlCmd.ExecuteReader();
    }
 private void sqlDep_OnChange(object sender, SqlNotificationEventArgs e)
    {
        // FROM: http://msdn.microsoft.com/en-us/a52dhwx7.aspx

        #region
        // This event will occur on a thread pool thread.
        // Updating the UI from a worker thread is not permitted.
        // The following code checks to see if it is safe to
        // update the UI.

        /* ISynchronizeInvoke i = (ISynchronizeInvoke)this;

         // If InvokeRequired returns True, the code
         // is executing on a worker thread.
         if (i.InvokeRequired)
         {
             // Create a delegate to perform the thread switch.
             OnChangeEventHandler tempDelegate = new OnChangeEventHandler(sqlDep_OnChange);

             object[] args = { sender, e };

             // Marshal the data from the worker thread
             // to the UI thread.
             i.BeginInvoke(tempDelegate, args);

             return;
         }*/
        #endregion

        // Have to remove this as it only work's once
        SqlDependency sqlDep = sender as SqlDependency;
        sqlDep.OnChange -= sqlDep_OnChange;

        // At this point, the code is executing on the
        // UI thread, so it is safe to update the UI..

        // 1) Resetup Dependecy
        SetupSQLDependency();

    }

Answer 1

You can hook up the SqlDependency.Change event and do whatever you like in this event handler. This is, in fact, the only way to do what you want, and there is nothing wrong with it.

In pseudo-code it looks like this:

var dep = new SqlDependency(GetSqlQueryForMonitoring());
dep.Change += () => {
 var data = ExecSql(GetDataQuerySql());
 UpdateCache(data);
};

Very simple. Just use two different queries.

Edit: In your sample code there is a comment saying you are running on the UI thread. Why should that be the case? I doubt it. Anyway, you should run your query before you resetup the dependency because otherwise you will have the potential for concurrent invalidations happening.

I suggest you get your fresh data from the database and then send a message to the ui to update it (Invoke).