SqlDependency.OnChange is not firing with filter on datetime column

Question

I am having issue using this SQL statement in SqlDependency. It simply not activating the SqlDependency.OnChange event, however returning the results as expected on SQL Server Query window.

SELECT [Order].OrderId
FROM [dbo].[Order]
WHERE [Order].CreatedOn > '20150815 21:11:57.502'

I read the Supported SELECT Statements section here https://technet.microsoft.com/en-us/library/ms181122(v=sql.105).aspx but didn't find any rule violated.

Any ideas?

Update:

My full code below:

    private void CheckForNewOrders(DateTime dt)
    {
        string json = null;
        string conStr = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;

        using (SqlConnection connection = new SqlConnection(conStr))
        {
            string query = string.Format(@"
                    SELECT [Order].OrderId
                    FROM [dbo].[Order]
                    WHERE [Order].CreatedOn > '{0}'"
, dt.ToString("yyyyMMdd HH:mm:ss.fff"));  // 20150814 00:00:00.000

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Notification = null;
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    json = "testing ... "; reader[0].ToString();
                }
            }
        }
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            CheckForNewOrders(DateTime.Now);
        }
    }

Update (in response of jmelosegui's answer):

            query = string.Format(@"
                    SELECT [Order].OrderId
                    FROM [dbo].[Order]
                    WHERE [Order].CreatedOn > @CreatedOn");

...

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.Add("@CreatedOn", SqlDbType.DateTime);
                command.Parameters["@CreatedOn"].Value = DateTime.Now;

                command.Notification = null;
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                ...
                }
            }

Thank you for adding your fix so clearly. This was exactly my issue and your solution works. — ManInMoon, Sep 21 '17 at 08:34

Juan M. Elosegui · Accepted Answer · 2015-08-15T17:42:16.017

3

I think you should be getting the notification event just not that type.

Could you add an else branch to your dependency_OnChange method to see if you are getting any other SqlNotificationType, such as:

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Change)
    {
        CheckForNewOrders(DateTime.Now);
    }
    else
    {
        //Do somthing here
        Console.WriteLine(e.Type);
    }
}

Update

Could you try to add a typed parameter:

WHERE [Order].CreatedOn > @myDateTime

And pass in a parameter of type DateTime instead of using the string conversion.

edited Aug 15 '15 at 17:42

answered Aug 15 '15 at 17:08

Juan M. Elosegui

6,471
5
35
48

I just tried your suggestion and found that OnChange event is not firing at all. But, as soon as I remove the where condition it works as expected. – Khadim Ali Aug 15 '15 at 17:22
Please see the code update above at the bottom of the originating post. It started firing OnChange event when I insert a new record in the table (that is updated with CreatedOn column with GETDATE() as a default table column value. `BUT` the reader.HasRows always returns false even the `SqlNotificationEventArgs.Type is Change`. – Khadim Ali Aug 15 '15 at 20:02
Will open another thread for the another problem as the original issue was resolved by your answer. Thanks. – Khadim Ali Aug 16 '15 at 16:21

score 0 · Answer 2 · edited Nov 16 '15 at 14:28

First of all, check all sql dependency services limitations. The where clause is not working in sql dependency events when sql table changes will fire. Your query is should be modified.

query = string.Format(@"
                SELECT [Order].OrderId
                FROM [dbo].[Order]
                ORDER BY [dbo].[Order].DateTime desc
        );

Please apply these changes and debug the project.

SqlDependency.OnChange is not firing with filter on datetime column

2 Answers2