基于SignalR和SqlDependency的个性化推送通知

Question

我们正在为Asp.net web应用程序添加实时推送通知。

我能够向所有登录到网站的用户广播一条信息。

但是，我无法根据插入到数据库表中的值向一个特定的用户发送通知。

当我尝试这样做时，它正在更新当前记录的所有客户端。

下面是我的代码示例：

SqlDependency组件：

Public Sub RegisterNotification(ByVal currentTime As DateTime)
    Try
        Dim conStr = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim sqlCommand = "SELECT [seq_id],[user_id],[create_timestamp],[alert_read] FROM [dbo].[tblAlerts]  WHERE [alert_read]=0 AND [create_timestamp] > @AddedOn"
        Using con As New SqlConnection(conStr)
            Dim cmd As New SqlCommand(sqlCommand, con)
            cmd.Parameters.AddWithValue("@AddedOn", currentTime)

            If con.State <> Data.ConnectionState.Open Then
                con.Open()
            End If
            cmd.Notification = Nothing
            Dim dependency As New SqlDependency(cmd)
            AddHandler dependency.OnChange, AddressOf sqlDep_OnChange
            Using reader As SqlDataReader = cmd.ExecuteReader()
                Do nothing here
            End Using
        End Using
    Catch ex As Exception
        Throw ex
    End Try
End Sub

Sub sqlDep_OnChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)
    Try
        If e.Info = SqlNotificationInfo.Insert Then
            Dim notificationHub = GlobalHost.ConnectionManager.GetHubContext(Of NotificationHub)
            Dim userid = Membership.GetUser.ProviderUserKey
            notificationHub.Clients.All.notify(userid)
        End If
        Dim depend = DirectCast(sender, SqlDependency)
        RemoveHandler depend.OnChange, AddressOf sqlDep_OnChange
        RegisterNotification(DateTime.UtcNow)
    Catch ex As Exception

    End Try
End Sub

通知中心代码

Public Class NotificationHub
    Inherits Hub

    Public Sub showdata(ByVal obj As Object)
        Try
            Dim userobj = obj
            Dim notificationHub = GlobalHost.ConnectionManager.GetHubContext(Of NotificationHub)
            Dim count = 0
            take count from database for userid in the object
            notificationHub.Clients.All.setcount(count)
        Catch ex As Exception

        End Try
    End Sub

End Class

SignalR Js代码

$(function () {

        // signalr js code for start hub and send receive notification
        var notificationHub = $.connection.notificationHub;

        notificationHub.client.setCount = function (data) {
            $('span.count').html(data);
        }                     

        $.connection.hub.start().done(function () {
            console.log('Notification hub started');
        });
        //signalr method for push server message to client
        notificationHub.client.notify = function (message) {
            if (message) {
                notificationHub.server.showdata(message);
            }
        }
    })

我还注意到这里还有一件事，如果我在多个浏览器中打开了应用程序，那么sqlDep_OnChange事件会被多次调用。

Answer 1

我对以下链接也做了同样的工作：https://learn.microsoft.com/en-us/aspnet/signalr/overview/guide-to-the-api/mapping-users-to-connections

使用SignalR v2，您可以使用connectionID找到相应的用户。