How to notify database changes using signalR
I just got started with SignalR and would like to try realtime notifications. The purpose is to display the updated message on the web page. There is a database table - DummyData
with a column Message
. This table has only one record - Hello
When the page is loaded, "Hello" is displayed.
Then I manually run the command in SQL Server 2012
update DummyData set Message='hello world'
but the message is not updated on the web page.
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 breakpoint is hit at first dependency_OnChange
and I can see new updated text coming back from SendNotification
. But this is not visible on the page. It looks like I'm almost there, but something is missing.
source to share
Signalr does not track 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 output, add something like 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 the connected clients.
You will need to disconnect in such a way that SignalR will catch, so you cannot just change the flag in the database. Try calling $.connection.hub.stop();
from your client.
This link details this in more detail.
If you say that the event is dependency_OnChange
triggered after an update in the database, then SendNotifications();
the hub method is called instead of calling , in particularNotifyAllClients(...)
source to share