Using SqlDependency leads to constant updates

Question

I pulled an example from this MSDN page and used it quite a bit verbatim. When the code works correctly, compilation is performed correctly, but changeCount infinitely increasing whether the data has really changed. When a change has occurred, dataGridView1 correctly reflects the change. Why does my SqlDependency seem like it is shooting in a loop, although apparently there were no changes?

Here's the source:

 #region Using directives using System; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Runtime.InteropServices; using System.Security.Permissions; using System.Text; using System.Windows.Forms; #endregion namespace PreAllocation_Check { public partial class Form1 : Form { int changeCount = 0; const string tableName = "MoxyPosition"; const string statusMessage = "Last: {0} - {1} changes."; DataSet dataToWatch = null; SqlConnection MoxyConn = null; SqlCommand SQLComm = null; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { if (CanRequestNotifications()) { SqlDependency.Start(GetConnectionString()); if (MoxyConn == null) MoxyConn = new SqlConnection(GetConnectionString()); if (SQLComm == null) { SQLComm = new SqlCommand(GetSQL(), MoxyConn); SqlParameter prm = new SqlParameter("@Quantity", SqlDbType.Int); prm.Direction = ParameterDirection.Input; prm.DbType = DbType.Int32; prm.Value = 100; SQLComm.Parameters.Add(prm); } if (dataToWatch == null) dataToWatch = new DataSet(); GetData(); } } private void Form1_FormClosed(object sender, FormClosedEventArgs e) { SqlDependency.Stop(GetConnectionString()); if (MoxyConn != null) MoxyConn.Close(); } private bool CanRequestNotifications() { try { SqlClientPermission SQLPerm = new SqlClientPermission(PermissionState.Unrestricted); SQLPerm.Demand(); return true; } catch { return false; } } private string GetConnectionString() { return "server=***;database=***;user id=***;password=***"; } private void GetData() { dataToWatch.Clear(); SQLComm.Notification = null; SqlDependency SQLDep = new SqlDependency(SQLComm); SQLDep.OnChange += new OnChangeEventHandler(SQLDep_OnChange); using (SqlDataAdapter adapter = new SqlDataAdapter(SQLComm)) { adapter.Fill(dataToWatch, tableName); dataGridView1.DataSource = dataToWatch; dataGridView1.DataMember = tableName; } } private string GetSQL() { return "SELECT PortID, CONVERT(money, SUM(PreAllocPos), 1) AS PreAllocation, CONVERT(money, SUM(AllocPos), 1) AS Allocation, CONVERT(money, SUM(PreAllocPos) - SUM(AllocPos), 1) AS PreLessAlloc " + "FROM MoxyPosition " + "WHERE CONVERT(money, PreAllocPos, 1) <> CONVERT(money, AllocPos, 1) " + "GROUP BY PortID " + "ORDER BY PortID ASC;"; } void SQLDep_OnChange(object sender, SqlNotificationEventArgs e) { ISynchronizeInvoke i = (ISynchronizeInvoke)this; if (i.InvokeRequired) { OnChangeEventHandler tempDelegate = new OnChangeEventHandler(SQLDep_OnChange); object[] args = { sender, e }; i.BeginInvoke(tempDelegate, args); return; } SqlDependency SQLDep = (SqlDependency)sender; SQLDep.OnChange -= SQLDep_OnChange; changeCount++; DateTime LastRefresh = System.DateTime.Now; label1.Text = String.Format(statusMessage, LastRefresh.TimeOfDay, changeCount); GetData(); } } } 

Edit: It is worth noting that the broker service is not currently enabled in the database with which I want to run this function, so to check my code, I backed up my target database and restored it with a new name, then performed ALTER DATABASE my_db_name SET ENABLE_BROKER against it. All my tests were on this alternative database, which means that I am the only user on it.

Answer 1

This is an old question, but the problem is that your request does not meet the requirements.

Short answer:
add the schema name to the table "FROM DBO.MoxyPosition " +

Longer answer:
Here you can see the list of requirements , which are very similar to the list of creating an indexed view. When an SQL dependency is registered, if it is invalid, the notification is triggered immediately, which allows you to invalidate it. When you think about it, it makes sense, because how can Visual Studio know what are the internal requirements for SQL Engine?

So, in your SQLDep_OnChange function SQLDep_OnChange you'll want to see the reason the dependency is running. The reason is in the variable e (information, source and type). Details about the event object can be found here:

• Information Parameters
• Source
• Type of

For your specific case, note how MS describes the Type property:

 Gets a value that indicates whether this notification is generated because of an actual change, OR BY THE SUBSCRIPTION. 

Answer 2

I had a similar problem. It turns out that executing SELECT * FROM dbo.MyTable caused a constant update. Switching to SELECT Id, Column1, Column2 FROM dbo.MyTable problem.

It doesn't look like you are using * in your request, but you can try to simplify your request to find out if you have a problem.

Answer 3

I have no answer for this, but you have violated at least one of the rules here: http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx

when you were unable to use two-part table names. Change MoxyPosition to dbo.MoxyPosition and review the rules above. I hope this helps, but something tells me that something else is to blame here.

Answer 4

look what type of SqlNotificationEventArgs is in your handler (as indicated below). If you see that it hits hundreds of times, and Type is a subscription every time, then your SQL statement is wrong - see Recommendations in other posts

 private void HandleOnChange(object sender, SqlNotificationEventArgs e) { ... var someType = e.Type; /*If it is Subscribe, not Change, then you may have your SQL statement wrong*/ ... }