UserMat2017 UserMat2017 - 3 years ago 157
ASP.NET (C#) Question

SqlDependency with SignalR user specific

Please follow code:

_Layout:

$(function () {
var connection = $.connection.notificationHub;

//signalr method for push server message to client
connection.client.addNotification = function (who) {
//send notification here
console.info("Send Notification")
};

// Start hub
$.connection.hub.start().done(function () {
console.log("SignalR Started")
});
});


Global.asax.cs:

public class Global : HttpApplication
{
string con = ConfigurationManager.ConnectionStrings["sqlConString"].ConnectionString;
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);

//here in Application Start we will start Sql Dependency
SqlDependency.Start(con);
}

protected void Session_Start(object sender, EventArgs e)
{
NotificationComponent NC = new NotificationComponent();
var currentTime = DateTime.Now;
HttpContext.Current.Session["LastUpdated"] = currentTime;
NC.RegisterNotification(currentTime);
}

protected void Application_End()
{
//here we will stop Sql Dependency
SqlDependency.Stop(con);
}
}


Controller:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult MyAction(string parameter)
{
//Database Notification (Table - Contacts) -Add or Update
ctx.SaveChanges();
}


NotificationHub : Hub

private readonly static ConnectionMapping<string> _connections = new ConnectionMapping<string>();

public static void SendNotification(string who)
{
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
foreach (var connectionId in _connections.GetConnections(who))
{
var result = context.Clients.Client(connectionId);
if (result != null)
{
result.addNotification(who);
}
}
}


Components.cs:

public void RegisterNotification(DateTime currentTime)
{
string conStr = ConfigurationManager.ConnectionStrings["sqlConString"].ConnectionString;
string sqlCommand = @"SELECT [ContactID],[ContactName],[ContactNo] from [dbo].[Contacts] where [AddedOn] > @AddedOn";
//you can notice here I have added table name like this [dbo].[Contacts] with [dbo], its mendatory when you use Sql Dependency
using (SqlConnection con = new SqlConnection(conStr))
{
SqlCommand cmd = new SqlCommand(sqlCommand, con);
cmd.Parameters.AddWithValue("@AddedOn", currentTime);
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
cmd.Notification = null;
SqlDependency sqlDep = new SqlDependency(cmd);
sqlDep.OnChange += sqlDep_OnChange;
//we must have to execute the command here
using (SqlDataReader reader = cmd.ExecuteReader())
{
// nothing need to add here now
}
}
}

//After code `ctx.SaveChanges()`, call the code below (50 times):
void SqlDep_OnChange(object sender, SqlNotificationEventArgs e) //<-- Here Problem
{

//from here we will send notification message to client
NotificationHub.SendNotification("User1586");
//...

//re-register notification
RegisterNotification(DateTime.Now);

//HERE -After this line "RegisterNotification(DateTime.Now);", it returns again to the line: "void SqlDep_OnChange(object sender, SqlNotificationEventArgs e)"
}


User "User1586" is receiving multiple notifications. 
The line
"void SqlDep_OnChange(object sender, SqlNotificationEventArgs e)"
repeats several times.
If you have 50 online users, do 50 times on this line:  

void SqlDep_OnChange(object sender, SqlNotificationEventArgs e)


If you have 1000 online users, do 1000 times on this line:  

void SqlDep_OnChange(object sender, SqlNotificationEventArgs e).


 In other words, user "User1586" receives several notifications.

I followed the example here: http://www.dotnetawesome.com/2016/05/push-notification-system-with-signalr.html

Idea is to send notification to a specific user after database update.

Any solution ?

Answer Source

First of all, Sql dependency is not aware what data has changed. So you should query inside the event handler. If you want to send data corresponding the user id and specifically just for one user, I would recommend you to do like this;

Event Handler

private void SqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Info == SqlNotificationInfo.Insert)
    {
         RecordInfo info = GetLastInsertedRecord(); //Just a custom entity
          if(info.UserId > 0)
             NotificationHub.SendNotification(info.UserId);
    }
    RegisterNotification(DateTime.Now);
}

Hub

public static List<UserConnection> ListUser { get; set; }
public static void SendNotification(string who)
{
    IHubContext context = GlobalHost.ConnectionManager.GetHubContext<MyHub>();  
    // Get specific user from connected ones.
    string Id = ListUser.Find(x => x.UserId == who).ConnectionId;
    context.Clients.Client(Id).addNotification(who); // or another data
}
//Add every connected users to the list
public override Task OnConnected()
    {
        ListUser = new List<UserConnection>();
        var us = new UserConnection();
        us.UserId = Context.QueryString["UserId"];
        us.ConnectionId = Context.ConnectionId;
        ListUser.Add(us);

        return base.OnConnected();
    }

UPDATE Client

$(function () {
    var connection = $.connection.notificationHub;
    //Pass the userId here as querystring  
    $.connection.hub.qs = "UserId=" + $("#labelHoldsUserId").val();
    //signalr method for push server message to client
    connection.client.addNotification = function (who) {
        //send notification here
        console.log(who + " sends message");
    };

    // Start hub
    $.connection.hub.start().done(function () {
        console.log("SignalR Started")
    });
})
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download