javascript - 根据客户端本身发送的标准，使用 SignalR 和 SQLDEPENDANCY 仅向特定用户推送通知

Question

我有一个 C# 应用程序，它使用 signalR 和 SQL 依赖项发送实时通知。

它可以很好地处理不依赖于用户的查询。

但现在，我只想向数据已更改的用户发送通知(其中 userid=@id)

SQL Server

USE [BlogDemos]
GO

/****** Object:  Table [dbo].[Messages]    Script Date: 10/12/2019 16:25:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Messages](
    [MessageID] [int] IDENTITY(1,1) NOT NULL,
    [userId] [nvarchar](50) NOT NULL,
    [Message] [nvarchar](50) NULL,
    [EmptyMessage] [nvarchar](50) NULL,
    [Date] [datetime] NULL,
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
    [MessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Messages] ADD  CONSTRAINT [DF_Messages_Date]  DEFAULT (getdate()) FOR [Date]
GO

C# 中的中心

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using MapUsers;
using Microsoft.AspNet.SignalR;
using Microsoft.AspNet.SignalR.Hubs;
using System.Data.Entity;

namespace SignalRDbUpdates.Hubs
{
    [HubName("notificationHub")]
    public class NotificationHub : Hub
    {
        Int16 MessageID = 0;
        string Message = "";
        string EmptyMessage = "";

        public void Hello()
        {
            Clients.All.hello();
        }



        [HubMethodName("sendNotifications")]
        public string SendNotifications()
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                string query = "SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages] " ;

                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Notification = null;
                    DataTable dt = new DataTable();
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();
                    var reader = command.ExecuteReader();
                    dt.Load(reader);
                    if (dt.Rows.Count > 0)
                    {
                        MessageID = Int16.Parse(dt.Rows[0]["MessageID"].ToString());
                        Message = (dt.Rows[0]["Message"].ToString());
                        EmptyMessage = (dt.Rows[0]["EmptyMessage"].ToString());
                        Date = DateTime.Parse(dt.Rows[0]["Date"].ToString());
                    }
                }
            }
            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
            return context.Clients.All.recievenotification(MessageID, Message, EmptyMessage, Date);
        }
        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {


                NotificationHub nHub = new NotificationHub();
                nHub.SendNotifications();
            }
        }
    }**

Startup.cs

using Microsoft.Owin;
using Owin;

[assembly: OwinStartupAttribute(typeof(SignalRDbUpdates.Startup))]
namespace SignalRDbUpdates
{
    public partial class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            ConfigureAuth(app);
            app.MapSignalR();   
        }
    }
}

Startup.Auth(允许来自不同服务器的客户端调用)

using Owin;
using Microsoft.Owin.Cors;
using Microsoft.AspNet.SignalR;

namespace SignalRDbUpdates
{
    public partial class Startup
    {
        // For more information on configuring authentication, please visit http://go.microsoft.com/fwlink/?LinkId=301864
        public void ConfigureAuth(IAppBuilder app)
        {
            app.UseCors(CorsOptions.AllowAll);
            app.MapSignalR(new HubConfiguration { EnableJSONP = true });
        }
    }
}

客户端

    <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="default.aspx.vb" Inherits="signalrsql._default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title>SignalR SQL Server dependancy Client</title>
    <style type="text/css">
        .auto-style1 {
            width: 166px;
        }
    </style>
</head>
    <script src='http://localhost:210/scripts/jquery-1.10.2.min.js' type="text/javascript"></script>     

    <script type="text/javascript">

        $.getScript('http://localhost:210/Scripts/jquery.signalR-2.1.1.min.js', function () {
            $.getScript('http://localhost:210/signalr/hubs', function () {
            // Declare a proxy to reference the hub.
            $.connection.hub.url = 'http://localhost:210/signalr';                
                var notifications = $.connection.notificationHub;

            // Create a function that the hub can call to broadcast messages.
            notifications.client.recievenotification = function (Id, Message, EmptyMessage, Date)
            {
                // Add the message to the page.                   

                $('#spanId').text(Id);
                $('#spanMessage').text(Message);
                $('#spanEmptyMessage').text(EmptyMessage);
                $('#spanDate').text(Date);
                };
                notifications.client.getValue = function (myId) {
                    notifications.returnValue(1);
                };

            // Start the connection.
                $.connection.hub.start().done(function () {
                    alert("connection started");
                notifications.server.sendNotifications();
            }).fail(function (e) {
                alert(e);
            });
            //$.connection.hub.start();
            });
        });
</script>


<body>
    <form id="form1" runat="server">

        <h1>Notifications</h1>

<div>
    <table border="1" style="width: 510px">
        <tr>
            <td class="auto-style1">Id</td>
            <td><span id="spanId"></span></td>
        </tr>
        <tr>
            <td class="auto-style1">Message</td>
            <td><span id="spanMessage"></span></td>
        </tr>
        <tr>
            <td class="auto-style1">EmptyMessage</td>
            <td><span id="spanEmptyMessage"></span></td>
        </tr>
        <tr>
            <td class="auto-style1">Date</td>
            <td><span id="spanDate"></span></td>
        </tr>

    </table>

</div>




    </form>

</body>
</html>

所以，我想通过添加条件来查询消息表

查询变为

字符串查询 = "SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages] where userId="+id ;

@id 将从客户端发送到服务器

如何实现这一目标？

感谢您的帮助。

Answer 1

将您的用户映射为 documented 。可能会考虑将用户插入单个用户组。在 SendNotifications() 中，而不是 Clients.All 发送到 Clients.User(userid) 或 Clients.Group(singleusergroupname)。

您还可以在带有 userid 结果的 SQL 查询之后使用 Clients.Caller()，这只会发送回请求它的客户端。