SQL dependency on C #

Question

I am trying to figure out how to use SQL Dependency (C # 4.0) to "listen" for database changes. I saw a lot of things on the Internet, but they seem to be adapted (naturally) to use the dependency to pull out the same data that the SQL dependency depends on. For example, in this article .

What I'm trying to do is create a dependency that, when launched, leads to many different SQL Select queries (which I can store in other methods, etc.). For example: I'm trying to establish a dependency that tracks the number of rows in a table. When the number of lines increases, then do x, y, z (i.e. my program does not care about the number of lines, it just increased, and when it does a bunch of things).

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

EDIT: I added my code as it is now. I am trying to figure out how to separate the SqlDependency setting from the GetData () process. Currently, however, I think I am entering an infinite loop, since after removing the event handler and re-running "SetupSqlDependency ()" it returns to 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 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 essentially the only way to do what you want, and there is nothing wrong with that.

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 that you are working in a user interface thread. Why should this be? I doubt it. In any case, you must run your query before you change the dependency, because otherwise you will have the opportunity for simultaneous invalid actions.

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