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



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