title: SQL Dependency in C#
body: <p>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, <a href="http://msdn.microsoft.com/en-us/a52dhwx7.aspx" rel="nofollow">this article</a>.</p>
  
  <p>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). </p>
  
  <p>Any thoughts on what would the best way to do this? </p>
  
  <p>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 </p>
  
  <pre><code>    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();
  
      }
  </code></pre>