This article explains how to get data from SQL Server and broadcast it using SignalR in ASP.NET.
As we all know SignalR is an ASP.Net server library for adding real-time functionality to a web application. This includes client libraries for JavaScript and other clients.
Getting Started
To get started with SignalR:
- Start Visual Studio
- Create a new website
- Provide the name and location of website
- Click "Next"
Install SignalR
Click "Tools" | "Library Package Manager" | "Package Manager Console" and run the command: "install-package Microsoft.AspNet.SignalR"
Or
Install using NuGet package Manager, right-click on "Project" and click on "Manage Nuget packages" and search for "SignalR" then click "Install".
This is the SQL Server database table design.
Table Data
Now add a new Hub class and add the following code:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Microsoft.AspNet.SignalR;
using Microsoft.AspNet.SignalR.Hubs;
namespace SignalR_SQLServer_Notification_Hubs
{
[HubName("notificationHub")]
public class NotificationHub : Hub
{
Int16 totalNewMessages = 0;
Int16 totalNewCircles = 0;
Int16 totalNewJobs = 0;
Int16 totalNewNotification = 0;
[HubMethodName("sendNotifications")]
public string SendNotifications()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
string query = "SELECT NewMessageCount, NewCircleRequestCount, NewNotificationsCount, NewJobNotificationsCount FROM [dbo].[Modeling_NewMessageNotificationCount] WHERE UserProfileId=" + "61764";
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Notification = null;
DataTable dt = new DataTable();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
dt.Load(reader);
if (dt.Rows.Count > 0)
{
totalNewMessages = Int16.Parse(dt.Rows[0]["NewMessageCount"].ToString());
totalNewCircles = Int16.Parse(dt.Rows[0]["NewCircleRequestCount"].ToString());
totalNewJobs = Int16.Parse(dt.Rows[0]["NewJobNotificationsCount"].ToString());
totalNewNotification = Int16.Parse(dt.Rows[0]["NewNotificationsCount"].ToString());
}
}
}
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
return context.Clients.All.RecieveNotification(totalNewMessages, totalNewCircles, totalNewJobs, totalNewNotification);
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change)
{
NotificationHub nHub = new NotificationHub();
nHub.SendNotifications();
}
}
}
}
Run application
Now add a Startup class.
using System.Web;
using Microsoft.Owin;
using Owin;
[assembly: OwinStartupAttribute(typeof(SignalR_SQLServer_Notification_MVC.Startup))]
namespace SignalR_SQLServer_Notification_MVC
{
public class Startup
{
public void Configuration(IAppBuilder app)
{
app.MapSignalR();
}
}
}
UI
<script src="~/Scripts/jquery-1.8.2.min.js" type="text/javascript" ></script>
<script src="~/Scripts/jquery.signalR-2.0.1.min.js" type="text/javascript" ></script>
<script src="~/signalr/hubs" type="text/javascript" ></script>
<script type="text/javascript">
$(function () {
// Declare a proxy to reference the hub.
var notifications = $.connection.notificationHub;
// Create a function that the hub can call to broadcast messages.
notifications.client.recieveNotification = function (totalNewMessages, totalNewCircles, totalNewJobs, totalNewNotifications) {
// Add the message to the page.
$('#spanNewMessages').text(totalNewMessages);
$('#spanNewCircles').text(totalNewCircles);
$('#spanNewJobNotifications').text(totalNewJobs);
$('#spanNewNotifications').text(totalNewNotifications);
};
// Start the connection.
$.connection.hub.start().done(function () {
notifications.server.sendNotifications();
}).fail(function (e) {
alert(e);
});
//$.connection.hub.start();
});
</script>
<h1>New Notifications</h1>
<div>
<b>You have <span id="spanNewMessages">0</span> New Message Notification.</b><br />
<b>You have <span id="spanNewCircles">0</span> New Circles Notification.</b><br />
<b>You have <span id="spanNewJobNotifications">0</span> New Job Notification.</b><br />
<b>You have <span id="spanNewNotifications">0</span> New Notification.</b>
</div>
Run application
