I've just started with SignalR and would like to try out the real time notifications. The objective is to keep displaying the count of active users on web page. Let's say the initial count of active users is 3. This is displayed on page.
Then I manually run an update command in database to deactivate a user. The count on page should change to 2 without refreshing, right? It still shows 3.
using asp.net, c#, signalR & sql server 2012
aspx: <script type="text/javascript">
$(function () {
var proxy = $.connection.serverHub;
proxy.client.broadcastCount = function (count) {
$("#spCount").text(count);
};
$.connection.hub.start().done(function () {
proxy.server.getCount();
});
});
</script>
<span id="spCount"></span>
C#: public class ServerHub:Hub
{
public void GetCount()
{
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<ServerHub>();
context.Clients.All.broadcastCount(DB.GetCount());
}
public override System.Threading.Tasks.Task OnConnected()
{
// Increase the active user count in the db
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<ServerHub>();
context.Clients.All.broadcastCount(DB.GetCount());
return base.OnConnected();
}
public override System.Threading.Tasks.Task OnDisconnected(bool stopCalled)
{
//Decrease the connected user count in the db
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<ServerHub>();
context.Clients.All.broadcastCount(DB.GetCount());
return base.OnDisconnected(stopCalled);
}
}
public class DB
{
public int GetCount()
{
SqlConnection con = new SqlConnection(DBConnection.ConnectionString);
SqlCommand cmd = new SqlCommand("usp_GetCount", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Notification = null;
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0]["UserCount"]);
}
return 0;
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change)
{
ServerHub obj = new ServerHub();
obj.GetCount();
}
}
}
global.asax: protected void Application_Start(object sender, EventArgs e)
{
SqlDependency.Start(DBConnection.ConnectionString);
}
protected void Application_End(object sender, EventArgs e)
{
SqlDependency.Stop(DBConnection.ConnectionString);
}
stored procedure: alter proc usp_GetCount
as
begin
select count(1) as UserCount from Users where Active=1
end