Sql Dependency - Notification received before processing current request

Question

I am currently using sql dependency notification to detect changes in a table and process them. I am having a problem where the notification gets called while its still in the middle of completing the first request which causes duplicate processing

private void ProcessData()
    {
        try
        {

                m_Guids = new List<Guid>();
                using (SqlCommand command = new SqlCommand("SP_XXX_SELECT", m_sqlConn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Notification = null;

                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
                    SqlDependency.Start(m_ConnectionString, m_QueueName);

                    if (m_sqlConn.State == ConnectionState.Closed)
                    {
                        m_sqlConn.Open();
                    }

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                m_Guids.Add(reader.GetGuid(0));
                            }
                        }
                    }
                    Console.WriteLine(m_Guids.Count.ToString());
                    ProcessGuids();
                }
            }
        }
        catch (Exception ex)
        {                
            //SendFailureEmail
        }
    }

private void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
    SqlDependency dependency = sender as SqlDependency;
    dependency.OnChange -= OnDependencyChange;
    ProcessData();
}    

public void OnStart()
{
    SqlDependency.Stop(m_ConnectionString, m_QueueName);
    SqlDependency.Start(m_ConnectionString, m_QueueName);
    m_sqlConn = new SqlConnection(m_ConnectionString);
}

ProcessData method gets called again while its still in the middle of processing (processGuids) Should I subscribe to the event after processing all the data? If I don't subscribe until processing is complete, what happens to the data that was changed during the process, which I believe doesn't get notified until next change happens?. What is the correct way of doing this or am I doing something wrong. Thanks

Answer 1

SqlDependency.OnChange is called not only on data change.

In the OnDependencyChange you must check e.Type/e.Source/e.Info.

F.e., combination of {Type = Subscribe, Source = Statement, Info = Invalid} means "Statement not ready for notification, no notification started". See Creating a Query for Notification for SQL statement requirements for notification. You must follow these requirements in SELECT statements in your SP.

Additional requirements for stored procedures are not well documented. Known restrictions for SP:

• Use of SET NOCOUNT (ON and OFF) is prohibited.
• Use of RETURN is prohibited.