在查询中使用COUNT_BIG()时，SqlDependency数据泛滥

Question

修复:代码更新，现在可以工作了

尝试为管理仪表板设置websocket，我需要使用count_big()字段和GROUP BY子句进行查询。标准记录集列表工作得很好，但是一旦我添加了count_big()， websocket就不会停止发送数据。我读过这篇关于限制的文章，count_big()似乎可以使用。TIA

    using Microsoft.Web.WebSockets;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Net;
    using System.Net.Http;
    using System.Web;
    using System.Web.Http;
    namespace DatabaseChangeNotification.Controllers
{
    public class DatabaseNotificationController : ApiController
    {
        public HttpResponseMessage Get()
        {
            HttpContext.Current.AcceptWebSocketRequest(new ChatWebSocketHandler());
            return Request.CreateResponse(HttpStatusCode.SwitchingProtocols);
        }
        class ChatWebSocketHandler : Microsoft.Web.WebSockets.WebSocketHandler
        {
            public string wsData = null;
            public SqlCommand gblCommand = null;
            public ChatWebSocketHandler()
            {
                SetupNotifier();
            }
            protected void SetupNotifier()
            {
                 using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
                {
                    connection.Open();
                    // DO NOT USE any "*" in queries
                    // WHen using count the variable was converted to string.  Got Data flood
                    // 
                    // Testing count_big data type
                    //  getString failed
                    //
                     using (SqlCommand command = new SqlCommand(@"select [address], count_big(*) as [CurrentTotal] from dbo.users where address = 'main st' group by address", connection)) 
                    {
                        command.Notification = null;
                        SqlDependency dependency = new SqlDependency(command);
                        dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                        if (connection.State == ConnectionState.Closed)
                        {
                            connection.Open();
                        }
                        //SqlCommand gblCommand = command;
                        wsData = null;
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                /* MUST MATCH column count and column data type */
                                // wsData += reader.GetString(0) + " " + reader.GetString(1) + " " + reader.GetString(2);
                                /* THIS WORKS FOR GETTING NUMERIC VARIABLES */
                                 wsData += reader.GetValue(0) + " " + int.Parse(reader.GetValue(1).ToString());
                                // wsData += reader.GetString(0) + "</br>";  //works but we get data flood and no numbers

                            }
                            // reader.Close();
                        }
                        _chatClients.Broadcast("data: " + wsData);
                    }
                }
            } //SetupNotifier
            private static WebSocketCollection _chatClients = new WebSocketCollection();
            public override void OnOpen()
            {
                _chatClients.Add(this);
            } // OnOpen
            public override void OnMessage(string msg)
            {
             } // OnMessage
            private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
            {
             if (e.Type != SqlNotificationType.Change)
            {
                _chatClients.Broadcast("Returning, not a change notification ");
                return;
            }
               /*
                * Must remove dependency. Only works once.
                */
                SqlDependency dependency = sender as SqlDependency;
                dependency.OnChange -= dependency_OnChange;
                 //reset for next message.
                 SetupNotifier();
            } // dependency_OnChange
        } // ChatWebSocketHandler
    } // DatabaseNotificationController
}

注意:这发生在代码被修复之前。

Web Socket returns infinite listing:
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    data: main st 1
    { .....}

Answer 1

您必须检查SqlNotificationEventArgs成员。并非所有通知都表示更新。一些通知(如您正在收到的通知)指示无效的条件或无效的查询。您将收到无效查询和重新提交的通知，只是出于相同的原因立即收到通知。令人作呕。

检查通知将指出问题所在。在你的情况下，问题在你自己发布的链接的第一个要点中列出:

表名必须由两部分组成