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)
                {
                ...
                }
            }

Answer 1

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.

Answer 2

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.