Query Notification - Duplicate OnChange Events Fired

Hi all,



I am investigating using Query Notifications - a great addition BTW. I have built a test app - loosely based on MSDN example - and am running against a SQL Express 2005 server. I have the following problem:



I have start/stop buttons to enable change checking, and a hardcode query that I am using for testing. If I stop and restart I now recieve duplicate notifcations, a single change causing the OnChange event to fires twice with two different ids Repeat this and the event will fires 3 times for each change and so on.

This only happens with a running app, if I restart the app I only get a single notification so I assume I have missed a step in stopping query notifcations or reinitialisation. I have include code below:



private bool Start()

{

try

{

// Remove any existing dependency connection, then create a new one.

SqlDependency.Stop(_currentConnectionString);

if (SqlDependency.Start(_currentConnectionString))

{

if (connection == null)

{

connection = new SqlConnection(_currentConnectionString);

}

if (command == null)

{

command = new SqlCommand(_sqlQueryString, connection);

}

return (true);

}

}

catch (Exception e)

{

MessageBox.Show(e.Message);

}

return false;

}

private void Stop()

{

SqlDependency.Stop(_currentConnectionString);

if (command != null)

{

command.Notification = null;

command = null;

}

if (connection != null)

{

connection.Close();

connection = null;

}

}

private void GetDataSnapshot()

{

// Empty the dataset so that there is only

// one batch of data displayed.

dataToWatch.Clear();

// Make sure the command object does not already have

// a notification object associated with it.

command.Notification = null;

// Create and bind the SqlDependency object

// to the command object.

SqlDependency dependency = new SqlDependency(command);

dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

using (SqlDataAdapter adapter = new SqlDataAdapter(command))

{

adapter.Fill(dataToWatch, tableName);

}

}

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)

{

// 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(dependency_OnChange);

object[] args = { sender, e };

// Marshal the data from the worker thread

// to the UI thread.

i.BeginInvoke(tempDelegate, args);

return;

}

// Remove the handler, since it is only good

// for a single notification.

SqlDependency dependency = (SqlDependency)sender;

dependency.OnChange -= dependency_OnChange;

// At this point, the code is executing on the

// UI thread, so it is safe to update the UI.

++changeCount;

lblStatus.Text = String.Format(statusMessage, changeCount);

// Add information from the event arguments to the list box

// for debugging purposes only.

//lbChanges.Items.Clear();

lbChanges.Items.Add("+++++++++++++++++");

lbChanges.Items.Add("Id: " + dependency.Id);

lbChanges.Items.Add("Info: " + e.Info.ToString());

lbChanges.Items.Add("Source: " + e.Source.ToString());

lbChanges.Items.Add("Type: " + e.Type.ToString());

// Reload the dataset that is bound to the grid.

if (e.Info != SqlNotificationInfo.Error)

this.GetDataSnapshot();

}



Thanks, Nick