How to notify database changes with signalR [Resolved]

Posted by Sharpcnet under C# on 7/27/2015 | Points: 10 | Views : 1530 | Status : [Member] | Replies : 1
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





Responses

Posted by: Rajnilari2015 on: 8/18/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Two things

a) You need to enable Service Broker on the database e.g. if your database name is TestDB then issue the below command

ALTER DATABASE TestDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;


b) Instead of

SqlCommand cmd = new SqlCommand("usp_GetCount", con);


use

SqlCommand cmd = new SqlCommand("dbo .usp_GetCount", con);


Let us know if you still face any further difficulties.

--
Thanks & Regards,
RNA Team

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response