Registering SQL Dependency again from onchange event

Question

i like to know is it right approach to registering SQL Dependency again from onchange event ?

one guy review my code and told me registering everything again and again after each notification. As I can recall this will create a queue each time

so i comment this code in onchange event

void OnDataChange(object sender, SqlNotificationEventArgs e)
        {
            BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);

            if (e.Source == SqlNotificationSource.Timeout)
            {
                BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
                Environment.Exit(1);
            }
            else if (e.Source != SqlNotificationSource.Data)
            {
                BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
                Environment.Exit(1);
            }
            else if (e.Type == SqlNotificationType.Change)
            {
                BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
            }
            else
            {
                BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
            }

            CallWebService();
            //((SqlDependency)sender).OnChange -= OnDataChange;
            //RegisterNotification();
        }

just see i comment this line

    //((SqlDependency)sender).OnChange -= OnDataChange;
    //RegisterNotification();

but after commenting i found onchange event is firing first time and it is not firing from second time.

guide me with right approach. here is my partial code

void OnDataChange(object sender, SqlNotificationEventArgs e)
{
    BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);

    if (e.Source == SqlNotificationSource.Timeout)
    {
    BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
    Environment.Exit(1);
    }
    else if (e.Source != SqlNotificationSource.Data)
    {
    BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
    Environment.Exit(1);
    }
    else if (e.Type == SqlNotificationType.Change)
    {
    BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
    }
    else
    {
    BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
    }

    CallWebService();
    //((SqlDependency)sender).OnChange -= OnDataChange;
    //RegisterNotification();
}


private void RegisterNotification()
    {
        string tmpdata = "";
        BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);

        System.Data.SqlClient.SqlDependency.Stop(connectionString);
        System.Data.SqlClient.SqlDependency.Start(connectionString);

        try
        {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT ActivityDate FROM [dbo].tablename";
            dep = new SqlDependency(cmd);
            dep.OnChange += new OnChangeEventHandler(OnDataChange);
            SqlDataReader dr = cmd.ExecuteReader();
            {
            if (dr.HasRows)
            {
                dr.Read();
                tmpdata = dr[0].ToString();
            }
            }
            dr.Dispose();
            cmd.Dispose();
        }
        }
        catch (Exception ex)
        {
        BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
        }
        finally
        {
        BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);

        }

}

i use Environment.Exit(1); to shutdown win service as a result it should restart automatically. thanks thanks

Answer 1

According to this article re-subscription is the right approach for changes monitoring in SQL server tables:

Note how, similarly to the SqlNotification usage, the client is expected to subscribe again if it whishes to be further notified.

Even this confusing example from CodePoject.com makes re-subscription (hidden in MessageModel.cs):

void notifier_NewMessage(object sender, SqlNotificationEventArgs e)
{
    // Indeed, the RegisterDependency call in the event hanler!
    this.LoadMessage(this.Notifier.RegisterDependency());
}

And take a look at the what the inventor saying about it.

As I told you before SqlDependency has the problems with behavior, usage and memory leaks. Try to use open source classes like SqlDependencyEx. I solved a lot of problems using it. With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. Hope this help.