How to notify database changes with signalR

Question

I've just started with SignalR and would like to try out the real time notifications. The objective is to keep displaying the updated message on web page. There is a database table - DummyData with a column Message. This table has only one record - Hello When the page loads, "Hello" is displayed.

I then manually run the command in sql server 2012

update DummyData set Message='hello world', but the message isn't updated in the webpage.

aspx:

<script>
    $(function () {
        var notify = $.connection.notificationsHub;

        $.connection.hub.start().done(function () {
            notify.server.notifyAllClients();
        });

        notify.client.displayNotification = function (msg) {               
            $("#newData").html(msg);
        };

        notify.client.stopClient = function () {
            $.connection.hub.stop();
        };
    });
</script>
 <span id="newData"></span>

aspx.cs:

public string SendNotifications()
    {
      string message = string.Empty;
      using (SqlConnection connection = new SqlConnection(conStr))
       {
        string query = "SELECT [Message] FROM [dbo].[DummyData]";

        SqlCommand command = new SqlCommand(query, connection)
        command.Notification = null;
        SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
         reader.Read();
         message = reader[0].ToString();
        }
       }            
        return message;
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            SendNotifications();
        }            
    }

NotificationsHub.cs

public class NotificationsHub : Hub
{
 Messages obj = new Messages();
 public void NotifyAllClients(string msg)
  {
   IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationsHub>();
   context.Clients.All.displayNotification(msg);
  }

 public override System.Threading.Tasks.Task OnConnected()
  {
   NotifyAllClients();
   return base.OnConnected();
  }

 public override System.Threading.Tasks.Task OnDisconnected(bool stopCalled)
 {
  NotifyAllClients();
  return base.OnDisconnected(stopCalled);
 }
}

global.asax:

protected void Application_Start(object sender, EventArgs e)
        {
            SqlDependency.Start(Constr);
        }

When I run the tsql update command, the break point is first hit at dependency_OnChange and I can see the new updated text being returned from SendNotification. But it isn't seen reflected on page. Feels like I'm almost there but something is missing.

Solution

Signalr is not watching your database for changes. So when you just set the user to inactive in the database, it means nothing to Signalr. Your 3 clients are still connected.

To get the desired result add something like this to your Hub

public override OnConnected()
{
  // Increase the active user count in the db 
  IHubContext context = GlobalHost.ConnectionManager.GetHubContext<ServerHub>();
  Clients.All.broadcastCount(DB.GetCount());
  return base.OnConnected();
}

public override OnDisconnected() 
{
    //Decrease the connected user count in the db
  IHubContext context = GlobalHost.ConnectionManager.GetHubContext<ServerHub>();
  Clients.All.broadcastCount(DB.GetCount());
  return base.OnDisconnected();
}

Then when you connect and disconnect your clients, the hub will notify connected clients.

You will need to disconnect in a way that SignalR will catch, so you can't just change a flag in the database. Try calling $.connection.hub.stop(); from your client.

This link goes into more detail on it.

If you say the dependency_OnChange event is fired after you update in the database, then Instead of calling SendNotifications();, call a hub method, specifically NotifyAllClients(...)