none
SqlDependency - OnChange event is going infinite loop while setting up, please find the code below RRS feed

  • Question

  • On the Service start event, I have the below code:

      protected override void OnStart(string[] args)
            {
                Logger.Info("start service");  
                SqlDependency.Stop(ConfigHelper.GetWebConfigValue("ConnectionString"));
                SqlDependency.Start(ConfigHelper.GetWebConfigValue("ConnectionString"));
                StartSqlDependency();
            }
      private void StartSqlDependency()
            {
                SqlConnection conn = new SqlConnection(ConfigHelper.GetWebConfigValue("ConnectionString"));
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("Select Top 1 EmailQueueId  From EmailQueue", conn))
                {
                    SqlDependency dependency = new SqlDependency(cmd);
                    dependency.OnChange += OnDataChange;
                    cmd.Dispose();
                }
            }
    private void OnDataChange(object sender, SqlNotificationEventArgs e)
            {
                SqlDependency dependency = (SqlDependency)sender;
                dependency.OnChange -= OnDataChange;
                StartSqlDependency();
            }

    The problem is that when we call the method StartSqlDependency(), OnDataChange is called immediately and it's going on infinite loop.

    If I execute the above code from a web project or windows form project, it doesn't loop and onChange get called whenever there is a insert/update on the EmailQueue table. 

    Any help to fix this will be a great help and appreciated.

    Friday, October 28, 2016 11:00 AM

Answers

  • Hi Praveen,

    >> If I execute the above code from a web project or windows form project, it doesn't loop and onChange get called whenever there is a insert/update on the EmailQueue table.

    Did you use the same query statement for winform and windows service? I made a test with a similar select command, and I will get infinite loop. After checking the result, I found you used Top in your query which is not supported in Select statement. If you debug your windows service on this line “private void OnDataChange(object sender, SqlNotificationEventArgs e)”, I think you will get invalid error.

    For more information about Select statement, I suggest you refer the link below:
    #Supported SELECT Statements
    https://msdn.microsoft.com/en-us/library/ms181122.aspx

    I would suggest you try something like below:

    Select Alias From [dbo].[tblUT] Where Id = 3

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, November 5, 2016 8:29 AM
  • Hi Edward,

    The issue is resolved and it's working now. Two things I did as follows:

    1. instead of * (asterik) in query, I changed it to have columns in the select query
    2. Tables created after enabling broker service doesn't work. You need to disable broker service and create a table, then, re-enable it. Below the script to enable it.
    ALTER DATABASE ServiceBrokerTest2 SET ENABLE_BROKER
    GO
    
    Creating Queue
    --------------
    CREATE QUEUE SBInitiatorQueue
    GO
    
    Creating Service and assigining the Queue
    ------------------------------------------
    CREATE SERVICE SBInitiatorService
    ON QUEUE SBInitiatorQueue
    GO
    

    Thanks a lot for your help on this, we can consider this thread as resolved.

    Regards,

    Praveen.V.Nair


    Friday, November 11, 2016 1:02 PM

All replies

  • Hi Praveen,

    You could use the wait for status method before calling the StartSqlDependency function.

    svc.Start();
    svc.WaitForStatus(ServiceControllerStatus.Running);


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Friday, October 28, 2016 12:44 PM
  • Hi Sabah,

    svc.Start();
    svc.WaitForStatus(ServiceControllerStatus.Running);

    the above code is for the waiting of the service. In my case, service is started properly, only problem is that my OnChange event is looping without waiting for table insert/update.

    Regards,

    Praveen.V.Nair

    Friday, October 28, 2016 12:53 PM
  • Hi Praveen.V.Nair

    >> it doesn't loop and onChange get called whenever there is a insert/update on the EmailQueue table. 

    From above message, What you probably want to do is use a Timer to do a loop (e.g. every 40 seconds) and do some work if some condition is matched e.g.


    private static Timer timer;
    private const int TimerInterval = 30000;
    
    protected override void OnStart(string[] args)
    {
        var callback = new TimerCallback(dosomethings);
        this.timer = new Timer(callback, null, 0, TimerInterval);
    }
    
    private void dosomethings()
    {
           //do what you wan to do 
    
    }

    And SqlDependency.OnChange Event occurs when a notification is received for any of the commands associated with this SqlDependency object.

    If I misunderstood you, please feel free to let me know.

    Best regards,

    Kristin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Kristin Xie Monday, October 31, 2016 8:52 AM
    Monday, October 31, 2016 8:52 AM
  • Hi Kristin,

    My current implementation using the same way of using the timer. Since, timer fires at intervals, there is a delay with the response. I don't want to queue and to avoid this delay, I want to onchange event of sqldepency which is failing on windows service.

    Regards,

    Praveen.V.Nair

    Monday, October 31, 2016 10:11 AM
  • Hi Praveen.V.Nair,

    >>If I execute the above code from a web project or windows form project, it doesn't loop and onChange get called whenever there is a insert/update on the EmailQueue table. 

    If your project is web or winform, you may need to use ISynchronizeInvoke.BeginInvoke Method to execute the delegate on the thread that created this object asynchronously. The procedure needs to catch the event and switch from the worker thread to the UI thread.

    Please see the link here for full details:

    Using SqlDependency in a Windows Application 

    http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx

    Best regards,

    Kristin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Kristin Xie Wednesday, November 2, 2016 6:08 AM
    Wednesday, November 2, 2016 6:08 AM
  • Hi Kristin,

    I don't have any issues with Winform/web application. My main problem is now with Window service which is not working as expected.

    Regards,

    Praveen.V.Nair

    Wednesday, November 2, 2016 10:36 AM
  • Hi Praveen,

    I am trying to make a test with your description, it will take some time.

    Sorry for any inconvenience.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 4, 2016 9:52 AM
  • Hi Edward,

    Looking forward to your findings with your POC.

    Regards,

    Praveen.V.Nair

    Friday, November 4, 2016 9:56 AM
  • Hi Praveen,

    >> If I execute the above code from a web project or windows form project, it doesn't loop and onChange get called whenever there is a insert/update on the EmailQueue table.

    Did you use the same query statement for winform and windows service? I made a test with a similar select command, and I will get infinite loop. After checking the result, I found you used Top in your query which is not supported in Select statement. If you debug your windows service on this line “private void OnDataChange(object sender, SqlNotificationEventArgs e)”, I think you will get invalid error.

    For more information about Select statement, I suggest you refer the link below:
    #Supported SELECT Statements
    https://msdn.microsoft.com/en-us/library/ms181122.aspx

    I would suggest you try something like below:

    Select Alias From [dbo].[tblUT] Where Id = 3

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, November 5, 2016 8:29 AM
  • Hi Edward,

    I haven't got the error you mentioned while debug onDataChange() event.

    I tried again now with modified query with specific list of columns and where condition in query. Now, this OnDataChange() not firing at all. I ran update query again this particular table for the particular row.

    Regards,

    Praveen.V.Nair

    Monday, November 7, 2016 11:04 AM
  • Hi Pravee,

    Based on your original post, it works under winform project, but failed under Windows Service. Did you use the same query statement in these projects?

    Which query did you use for SqlCommand and which query did you use to update table? I suggest you use the same query in winform and windows service to check whether winform got notification, but windows service did not for the same update command.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 8, 2016 7:05 AM
  • Hi Edward,

    The issue is resolved and it's working now. Two things I did as follows:

    1. instead of * (asterik) in query, I changed it to have columns in the select query
    2. Tables created after enabling broker service doesn't work. You need to disable broker service and create a table, then, re-enable it. Below the script to enable it.
    ALTER DATABASE ServiceBrokerTest2 SET ENABLE_BROKER
    GO
    
    Creating Queue
    --------------
    CREATE QUEUE SBInitiatorQueue
    GO
    
    Creating Service and assigining the Queue
    ------------------------------------------
    CREATE SERVICE SBInitiatorService
    ON QUEUE SBInitiatorQueue
    GO
    

    Thanks a lot for your help on this, we can consider this thread as resolved.

    Regards,

    Praveen.V.Nair


    Friday, November 11, 2016 1:02 PM