Sql Dependency onchange event not firing every tim

Question

I have implemented sql dependency in windows service. when data will be changed in table then onchange event will fire and from there I am invoking a web service.

I will share my full code. I tested many time on my PC before installing the windows service on production PC it works. Suppose if I install on production PC today then it works for today but when I test next day then its onchange event was not firing.

so I found onchange event firing only first day and from the next day onchange event not firing. May be I have made some mistake in code. so it is my request please some one see my code in details and help me where I have made the mistake for which it is not working properly.

public partial class PartIndexer : ServiceBase
{
    static string connectionString = "server=xxx;uid=xxx;password=xxx;database=xxx;Pooling=true;Connect Timeout=20;";
    SqlDependency dep;

    public PartIndexer()
    {
        InitializeComponent();
    }

    private string GetLoggedInUser()
    {
        string userName = "";
        if (System.Security.Principal.WindowsIdentity.GetCurrent() != null)
        {
            userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
        }
        return userName;
    }

    #region OnStart
    protected override void OnStart(string[] args)
    {
        BBALogger.Write("PartIndexer Service OnStart called start", BBALogger.MsgType.Info);
        RegisterNotification();
        MailSend(); // notification mail send
        BBALogger.Write("PartIndexer Service OnStart called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
    }
    #endregion

    #region RegisterNotification
    /// <summary>
    /// RegisterNotification
    /// this is main routine which will monitor data change in ContentChangeLog table
    /// </summary>
    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 TestTable FROM [dbo].ContentChangeLog";
                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);

        }

    }
    #endregion

    #region OnDataChange
    /// <summary>
    /// OnDataChange
    /// OnDataChange will fire when after data change found in ContentChangeLog table
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void OnDataChange(object sender, SqlNotificationEventArgs e)
    {
        ((SqlDependency)sender).OnChange -= OnDataChange;

        BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);

        if (e.Source == SqlNotificationSource.Timeout)
        {
    MailSend(); // notification mail send
    BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);

            Environment.Exit(1);
        }
        else if (e.Source != SqlNotificationSource.Data)
        {
        MailSend(); // notification mail send
            BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);

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

        RegisterNotification();
    }
     #endregion

    #region StartIndex
    /// <summary>
    /// StartIndex
    /// this routine will call web service in bba reman website which will invoke routine to re-index data
    /// </summary>
    void StartIndex()
    {
        // calling web service if change is notified

    }
    #endregion

    #region MailSend
    /// <summary>
    /// MailNotify
    /// fire mail when apps start & exit
    /// </summary>
    /// <param name="strStatus"></param>
    void MailSend()
    {
    // mail send code
    }
    #endregion

    #region OnStop
    protected override void OnStop()
    {
        BBALogger.Write("PartIndexer Service StartIndex called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
        System.Data.SqlClient.SqlDependency.Stop(connectionString);
        MailNotify("STOPPED");
    }
    #endregion
}

Another issue I noticed that when I start my windows service and leave it run for one day then when I try to stop or restart the the windows service then I windows service cannot be stopped. It means definitely there is some flaw in my code which I am not being able to point out. so please help me. thanks

Answer 1

i just noticed that you used

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

sql dependency start and stop inside RegisterNotification() function, but its not correct way, and it may cause the problem to call event next time. it may work one time correctly.

you just try to use in windows start function

    #region OnStart
    protected override void OnStart(string[] args)
    {

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

        BBALogger.Write("PartIndexer Service OnStart called start", BBALogger.MsgType.Info);
        RegisterNotification();
        MailSend(); // notification mail send
        BBALogger.Write("PartIndexer Service OnStart called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
    }
    #endregion

i hope it will resolve your issue, thanks.