为什么SQL依赖项没有使用group by子句和top来触发事件？ [英] Why SQL dependency not firing event with group by clause and top ?

问题描述

嗨

我正在处理Signal r和sql依赖。我为这样的要求创建了一个演示，如果我在数据库中更新。它将自动更改ui内容。它工作正常，但当我试图使用group by子句或顶部或Rownum时。 Sql依赖项更改事件未触发，并且它不反映UI上的任何更改。

hi
i am working with Signal r and sql dependency . i have created one demo for such requirement that if i update in database . it will auto change ui content. its working fine but when i tried to use group by clause or top or Rownum . Sql dependency Change event is not firing and it does not reflect any change on UI.

readonly string _connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        public IEnumerable<productinfo> GetAllMessages()
        {
            var ProductInfo = new List<productinfo>();
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"[dbo].[GetAllRecordsWithParams]", connection))
                {
                    command.Notification = null;
                    command.CommandType = CommandType.StoredProcedure;
                    var dependency = new SqlDependency(command);
                    SqlDependency.Start(_connString);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        ProductInfo.Add(item: new ProductInfo
                        {
                            CustomerID = (string)reader["CustomerID"],
                            ContactName = (string)reader["ContactName"],
                            Phone = (string)reader["Phone"],
                            ProductName = (string)reader["ProductName"],
                            UnitPrice = (decimal)reader["UnitPrice"],
                            IsDeleted = (bool)reader["IsDeleted"]
                        });
                    }
                    
                }
                connection.Close();
                connection.Dispose();
            }
            return ProductInfo.ToList();
        }
        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                MessagesHub.SendMessages();
            }
        }

Sql更改::

Sql changes ::

ALTER PROCEDURE [dbo].[GetMixItems] 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	

    -- Insert statements for procedure here
	  select  dm.MessageID, dm.Message, dm.EmptyMessage, dm.Date, dt.MessageExtendedText,dt.IsRight
	   from [dbo].[Messages] as dm inner join [dbo].[TestTable] as 
	   dt on dm.MessageID  = dt.MessageTestId where IsRight = 0 
	   group by dm.MessageID, dm.Message, dm.EmptyMessage, dm.Date, dt.MessageExtendedText,dt.IsRight 
	   order by dm.MessageID desc
	  
END

我尝试了什么：



i尝试了我提到的所有内容..请告诉我解决方案。如何做这样的所有子句与sql依赖关系...

What I have tried:

i tried all i have mentioned .. please tell me d solution of this. how to do such all clause work with sql dependency...

推荐答案

SQL依赖关系类用于检测查询结果何时与最初检索的结果不同。因此，如果查询调用之间没有任何更改，则SqlDependency类不会通知用户有关更改的信息。



请参阅：

使用SqlDependency检测更改 [ ^ ]

SqlDependency Class（System.Data.SqlClient） [ ^ ]

SQL Dependency class is used to detect when query results differ from those originally retrieved. So, if there's no changes between query calls, SqlDependency class won't notify user about changes.

See:
Detecting Changes with SqlDependency[^]
SqlDependency Class (System.Data.SqlClient)[^]

比回答。但我已经提到，如果我使用group by，top，distinct这样的子句并尝试从数据库更新值，仍然没有触发事件......

Than for Answering. But i have already mentioned that if i am using group by , top, distinct such clause and trying to update value from database , still event is not firing...

这篇关于为什么SQL依赖项没有使用group by子句和top来触发事件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！