SqlDependency.OnChange触发，但SqlDataReader没有返回数据。

Question

当我执行带有日期时间列过滤器的查询时

WHERE [Order].CreatedOn >= @CreatedOn

通过使用 SqlDependency，当数据源发生变化时会触发 SqlDependency.OnChange 事件，但是与 SqlCommand 相关的 SqlDataReader 不返回数据（reader.HasRows 总是返回 false）。

当我只是改变 SQL 语句中的过滤条件时：

WHERE [Order].StatusId = 1"

这段代码运行良好，SqlDataReader 返回了数据（reader.HasRows 返回 true）。

代码：

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);
            }
        }
    }
}

图片:

这里展示了五张图片。

- Khadim Ali

---

那么，为什么SqlDependency.OnChange事件会触发？它是在每个表记录更改时触发还是在查询结果更改时触发？此外，您可以在上面的第3张图中看到，我有1条记录，创建时间为23:42:51。 - Khadim Ali

尝试将CreatedOn参数的值硬编码，看看是否能得到任何结果。 - Pankaj Kapare

正如@chankya所提到的，您的查询是错误的，CreatedOn将始终在过去，您正在检查CreatedOn>现在，这个条件永远不会被满足。 - Akash Kava

Answer 1

当从onchange事件调用CheckForNewOrders方法时

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

针对参数@CreatedOn，您正在传递的是DateTime.Now（而不是其更改时间）。数据库中将没有任何满足该条件的数据。

- Chanakya

---

我并没有理解你在Not the time of its change的意思。你能否提供一个例子来说明一下呢？此外，如果没有数据符合条件，那么为什么SqlDependency.OnChange事件会被触发，并且e.type=Change呢？（请查看我帖子中上面的第4张图片） - Khadim Ali

使用 SQL 依赖项，我们只能知道更改的内容，而不知道触发更改的记录。如果我们查看您的代码：SELECT [Order].OrderId FROM [dbo].[Order] WHERE [Order].CreatedOn >= @CreatedOn例如：假设依赖项是在 2015年08月19日 上午12:00:00 创建的。一条记录插入于 2015年08月19日 上午12:01:01。更改事件在 2015年08月19日 上午12:01:02 触发。但在 onchange 事件中，您正在使用 datetime.now 进行查询：SELECT [Order].OrderId FROM [dbo].[Order] WHERE [Order].CreatedOn >= 2015年08月19日 上午12:01:02这将没有任何数据。 - Chanakya

非常感谢@Chanakya。我完全同意你的答案并且也点了赞，但是另一个答案也包含了适用于我的情况的解决方案。 - Khadim Ali

Answer 2

当将DateTime.Now作为参考日期传递时，您不太可能检索到已在某个时间点创建的记录（除非记录是在未来创建的，因此您的服务器时间存在问题或者“createdOn”列名非常误导）。

要根据某个更新日期获取最新记录，需要执行以下操作：

- 创建一个全局变量来保存您已经检索过的最大创建日期（在我的示例中为_refDate，并初始化为您选择的值，我使用DateTime.MinValue在第一次调用中获取所有记录，然后仅逐步获取它们，您也可以使用DateTime.Now从某个时间点开始）； - 触发CheckForNewOrders查询； - 当您检索结果时，还要发送CreatedOn列并将检索到的最大CreatedOn日期保存为新的参考日期； - 当DB中的值更改且触发dependency_OnChange事件时，您需要使用_refDate的最后一个值触发查询，以便获取尚未检索的所有内容； - 然后再次更新_refDate的值，依此类推...

虽然未经测试，但这应该可以工作（请注意_refDate可在全局范围内访问）。

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