10

When I execute a query with a datetime column filter

WHERE [Order].CreatedOn >= @CreatedOn

using a SqlDependency, the change on data source fires the SqlDependency.OnChange event but the SqlDataReader associated with the SqlCommand doesn't return data (reader.HasRows always returns false).

When I just change the filter condition in my SQL statement to

WHERE [Order].StatusId = 1"

it just works fine and the SqlDataReader returns data (reader.HasRows returns true)

Code:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace SignalRServer
{
    public partial class DepartmentScreen : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            var u = System.Security.Principal.WindowsIdentity.GetCurrent().User;
            var UserName = u.Translate(Type.GetType("System.Security.Principal.NTAccount")).Value;

            CheckForNewOrders(DateTime.Now);
        }

        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 >= @CreatedOn");

                //                query = string.Format(@"
                //                        SELECT [Order].OrderId
                //                        FROM [dbo].[Order]
                //                        WHERE [Order].StatusId = 1");

                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)
                    {
                        reader.Read();
                        json = reader[0].ToString();
                    }
                }
            }

            SignalRHub hub = new SignalRHub();
            hub.OrderReceived(json, null);
        }

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

Images:

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Khadim Ali
  • 2,548
  • 3
  • 33
  • 61
  • Well, then why the SqlDependency.OnChange event is firng? Does it fire for every table records' change or it fires when the query results change? Moreover, you may see in the 3rd image above that I have 1 record with 23:42:51 as CreatedOn date. – Khadim Ali Aug 17 '15 at 16:30
  • Try hardcoding value of CreatedOn parameter and see if you get any result. – Pankaj Kapare Aug 19 '15 at 13:28
  • As @chankya has mentioned, your query is wrong, CreatedOn will always be in past, you are checking for CreatedOn > now, this condition will never be satisfied. – Akash Kava Aug 25 '15 at 05:26

2 Answers2

4

When the method CheckForNewOrders is called from onchange event

command.Parameters.Add("@CreatedOn", SqlDbType.DateTime);
command.Parameters["@CreatedOn"].Value = DateTime.Now;

For the parameter @CreatedOn you are passing DateTime.Now (Not the time of its change). There will not be any data satisfying the condition in database.

Anik Islam Abhi
  • 25,137
  • 8
  • 58
  • 80
Chanakya
  • 71
  • 4
  • I didn't get your point at `Not the time of its change`. Would you please provide an example for this? Furthermore, if there was no data matching the criteria, then why the `SqlDependency.OnChange` event fired with `e.type=Change`? (See the image no. 4 above in my post) – Khadim Ali Aug 19 '15 at 09:42
  • 1
    Using SQL dependency `we can only know the change but not the records that triggered the change`. If we see your code `SELECT [Order].OrderId FROM [dbo].[Order] WHERE [Order].CreatedOn >= @CreatedOn` For Example : Lets assume The dependency is created on `08/19/2015 12:00:00 AM`. A record is inserted on `08/19/2015 12:01:01 AM`. On change event is triggered at 08/19/2015 12:01:02 AM. But in the onchange event you are querying using datetime.now `SELECT [Order].OrderId FROM [dbo].[Order] WHERE [Order].CreatedOn >= 08/19/2015 12:01:02 AM` which will not have any data – Chanakya Aug 19 '15 at 12:12
  • Many thanks @Chanakya. I completely agree with your answer and upvoted it also, but the another answer included solution for my scenario also. – Khadim Ali Aug 26 '15 at 14:06
3

When passing DateTime.Now as a reference Date you are not very likely to retrieve records that have been created at some point in time (except if the records are created in the future and therefore you have some problem with your server time or the column name "createdOn" is very missleading).

To get the latest records based on some update date you need to do something like this:

  • create a global variable that saves the max created date that you have already retrieved (_refDate in my example, initialized to the value you choose, DateTime.MinValue in my case to get all records in the first call and then only get them incrementally, you can also take DateTime.Now to start at one moment in time)
  • trigger the CheckForNewOrders query
  • when you retrieve the results also send the CreatedOn column and save the maximum retrieved CreatedOn date as the new reference date
  • when the value changes in DB and the dependency_OnChange event gets triggered you need to trigger the query with the last value of _refDate in order to get everything you haven't retrieved yet
  • update the value of _refDate again and so on ..

Not tested but this should work (take care of _refDate to be acccessible globally)

public partial class DepartmentScreen : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            var u = System.Security.Principal.WindowsIdentity.GetCurrent().User;
            var UserName = u.Translate(Type.GetType("System.Security.Principal.NTAccount")).Value;

            CheckForNewOrders(_refDate);
        }

        private DateTime _refDate = DateTime.MinValue;

        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, [Order].CreatedOn
                    FROM [dbo].[Order]
                    WHERE [Order].CreatedOn >= @CreatedOn");

                //                query = string.Format(@"
                //                        SELECT [Order].OrderId
                //                        FROM [dbo].[Order]
                //                        WHERE [Order].StatusId = 1");

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

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

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            //json = reader[0].ToString();
                            var date = Convert.ToDateTime(reader["CreatedOn"]);

                            if (date > _refDate)
                            {
                                _refDate = date;
                            }
                        }
                    }
                }
            }

            //SignalRHub hub = new SignalRHub();
            //hub.OrderReceived(json, null);
        }

        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                CheckForNewOrders(_refDate);
            }
            else
            {
                //Do somthing here
                //Console.WriteLine(e.Type);
            }
        }
    }
}
Fabian
  • 1,886
  • 14
  • 13