Personalized push notification with SignalR and SqlDependency

Question

We are working adding real time push notification to Asp.net web application.

I'm able to broadcast one message to all the users who is logged in to website.

but I'm not able to send notification to only one particular user based on the value inserted in the database table.

when i try to do this it's updating all the clients whoever is logged currently.

My code sample below:

SqlDependency Component:

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

Notification Hub Code

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 code

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

I have also noticed one more thing here is , sqlDep_OnChange event is called more than once if i have opened applicaiton in more than one browser.

Steven Hamerling Steven Hamerling · Accepted Answer · 2017-08-09T16:43:25

I have managed to do the same with following link: https://docs.microsoft.com/en-us/aspnet/signalr/overview/guide-to-the-api/mapping-users-to-connections

Using SignalR v2 you can use the connectionID to find the corresponding user.

Personalized push notification with SignalR and SqlDependency

1 Answers