Broadcast SQL Data Using SignalR in ASP.Net

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".

 NuGet package Manager

This is the SQL Server database table design.

database table

Table Data

Table Data

Now add a new Hub class and add the following code:

Hub class

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

Error in Application

Run Successfully

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

Run application