Pushing new messages with SignalR and SqlDependency

Question

I have a large table containing items that generally has a few new items inserted every second. I want to build a realtime application where users can subscribe to certain types of items and have a realtime feed whenever an item of a certain type is inserted.

As a start, I used http://techbrij.com/database-change-notifications-asp-net-signalr-sqldependency but it is missing some crucial pieces that I am not sure how to implement.

What is the best way to track which messages to push to which clients? How can I track which messages have already been sent without the overhead of updating a "pushed" flag every time an update is received?

Ill have to follow along with the demo and take a look tonight if there is no answer. — RubberChickenLeader
– RubberChickenLeader, Commented Apr 16, 2015 at 15:38
What if I created a different sqldependency for each "type" of item currently being monitored? When a message is sent, the sqldependency query would be updated to where id>lastsent? Would this be the correct way to do it? — phosplait
– phosplait, Commented Apr 17, 2015 at 13:50
I took a quick look last night and could not find a better way than time stamps or a pushed flag. Preference would be time stamps of pushed date since you would then also have history of when things were pushed. — RubberChickenLeader
– RubberChickenLeader, Commented Apr 17, 2015 at 16:21

dyatchenko · Accepted Answer · 2015-04-21 09:44:24Z

You can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. The test project for this component does exactly the same functionality what you are looking for. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. Hope this help.

Spivonious · Accepted Answer · 2016-06-30 12:30:47Z

SignalR provides a Context.ConnectionId in the hub. You can use this as a key in a collection to track which items have been sent.

Here's an example from a logging chat application I wrote:

public class PushNotificationHub : Hub {        

    public void Send(string name, string message) {
        ChatService.ChatUsers[Context.ConnectionId].AddMessage(message);
        Clients.All.addMessage(name, message);
    }

    public override Task OnConnected() {
        ChatService.Current.LogMessage($"Client connected: {Context.ConnectionId}");
        ChatService.ChatUsers.Add(Context.ConnectionId, new Common.ChatUser(Context.QueryString["name"]));

        return base.OnConnected();
    }

    public override Task OnDisconnected(bool stopCalled) {
        //Application.Current.Dispatcher.Invoke(() => ((MainWindow)Application.Current.MainWindow).LogMessage($"Client disconnected: {Context.ConnectionId}"));
        ChatService.Current.LogMessage($"Client disconnected: {Context.ConnectionId}");
        ChatService.Current.LogMessage(ChatService.ChatUsers[Context.ConnectionId].PrintLog());

        return base.OnDisconnected(stopCalled);
    }
}

Collectives™ on Stack Overflow

Pushing new messages with SignalR and SqlDependency

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related