SqlDependency onchange event infinite loop

I have a simple query, and the event fires at the correct time. However, once fired, the property, .HasChanges, of the SqlDependency object is always set as true.

The first time OnChange is fired, the SqlNotificationEventArgs Info property is “Inserted”. The second time the event is fired it’s “Already Changed”.

I commented all of my code in the OnChange event out to verify that my code wasn’t causing the change.
Servicebroker is enabled in the database

Is there a reason the following code causes an infinite loop of onChange events?

static void Main()
{
    SqlDependency.Stop(Properties.Settings.Default.DEVConnectionString);
    SqlDependency.Start(Properties.Settings.Default.DEVConnectionString);

    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.DEVConnectionString))
    {
        cn.Open();

        using (SqlCommand cmd = new SqlCommand("SELECT UserPageActionLogID, PageActionID FROM dbo.UserPageActionLog WHERE PageActionID != 3 ORDER BY UserPageActionLogID ASC", cn))
        {
            cmd.Notification = null;

            SqlDependency dep = new SqlDependency(cmd);
            dep.OnChange += dep_onchange;

            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    //Do nothing on first run
                }
            }
        }
    }
    Application.Run(); //Prevents the application from closing
}

private static void dep_onchange(object sender, SqlNotificationEventArgs e)
{
    SqlDependency dependency = sender as SqlDependency;
    dependency.OnChange -= dep_onchange;

    //Do stuff for the function. I commented this out and still had an issue

    //Resubscribe to the event to continue catching future changes
    dependency.OnChange += dep_onchange;
}

​x
 
static void Main(){    SqlDependency.Stop(Properties.Settings.Default.DEVConnectionString);    SqlDependency.Start(Properties.Settings.Default.DEVConnectionString);​    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.DEVConnectionString))    {        cn.Open();​        using (SqlCommand cmd = new SqlCommand("SELECT UserPageActionLogID, PageActionID FROM dbo.UserPageActionLog WHERE PageActionID != 3 ORDER BY UserPageActionLogID ASC", cn))        {            cmd.Notification = null;​            SqlDependency dep = new SqlDependency(cmd);            dep.OnChange += dep_onchange;​            using (SqlDataReader dr = cmd.ExecuteReader())            {                while (dr.Read())                {                    //Do nothing on first run                }            }        }    }    Application.Run(); //Prevents the application from closing}​private static void dep_onchange(object sender, SqlNotificationEventArgs e){    SqlDependency dependency = sender as SqlDependency;    dependency.OnChange -= dep_onchange;​    //Do stuff for the function. I commented this out and still had an issue​    //Resubscribe to the event to continue catching future changes    dependency.OnChange += dep_onchange;}​

Answer

It appears that the both the OnChange handler and the SqlDependency instance are only good for ONE event. After the event is fired and you unsubscribe the handler, you need to register your handler to a NEW SqlDependency object.

Please see the link here for full details: http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx

Advertisement

Answer