Instant Database Changes Using SignalR

Introduction

SignalR is the technology by which we can add real time web functionality to applications. That means, the server pushes the content/data to the connected clients instantaneously without the server waiting for the client for making a new data request.

In one of our earlier article, we have seen Send periodic response from the server using SignalR in ASP.NET MVC. In this article, we will look into an interesting problem of reflecting instant Database changes using SignalR.

Straight to Experiment

Step 1: Database and Table Creation

Let us first create a database say ExperimentalDB and create a table [dbo].[Student] by issuing the below script

USE [ExperimentalDB]
GO

/****** Object:  Table [dbo].[Student]    Script Date: 8/19/2016 9:12:29 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Student](
	[StudentID] [int] IDENTITY(1,1) NOT NULL,
	[StudentName] [varchar](50) NOT NULL,
	[DOB] [datetime] NOT NULL,
	[Weight] [int] NULL,
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
	[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Step 2: Enable Service Broker on the database

Service Broker is a feature introduced for the first time in SQL Server 2005. By using this feature, external or internal processes can send and receive asynchronous messages reliably by using extensions of Transact-SQL Data Manipulation Language (DML). It is a queued and reliable messaging mechanism used for asynchronous programming model.

We need this feature to be enable since, whenever a change in the table will happen like (Insert/Update/Delete/Truncate), then the SQLDependency should be able to identify that. It (Service Broker) rather implements a Broker Architecture which publishes the events while the SQL Dependency acts as a subscriber and detects the changes. Using the SqlDependency object, the application can create and register to receive notifications via the OnChangeEventHandler event handler.

Now let us issue the below command to check if Service Broker is enabled for [ExperimentalDB]

SELECT NAME, IS_BROKER_ENABLED FROM SYS.DATABASES
WHERE NAME='ExperimentalDB'

Result
-------
NAME			IS_BROKER_ENABLED
--------------		-----------------
ExperimentalDB			0

Now, we will enable the Service Broker for [ExperimentalDB]

--Enable the Service Broker for ExperimentalDB
ALTER DATABASE ExperimentalDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;

Step 3: Let us create the MVC project

For this experiment, we will create a MVC project and our project structure looks as under

The underlined files are the one's which either we have Created or have Modified.

Let us first install SignalR from Nuget package by running the following command in the Package Manager Console

Install-Package Microsoft.AspNet.SignalR

Let us start with StudentRepository.cs file

using SignalRInstantDbChangesDemo.Hubs;
using SignalRInstantDbChangesDemo.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace SignalRInstantDbChangesDemo.DataAccess
{
    public class StudentRepository
    {
        public static List<Student> GetStudentRecords()
        {
            var lstStudentRecords = new List<Student>();
            string dbConnectionSettings = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (var dbConnection = new SqlConnection(dbConnectionSettings))
            {
                dbConnection.Open();

                var sqlCommandText = @"SELECT [StudentID],[StudentName],[DOB],[Weight] FROM [dbo].[Student]";

                using (var sqlCommand = new SqlCommand(sqlCommandText, dbConnection))
                {
                    AddSQLDependency(sqlCommand);                   

                    if (dbConnection.State == ConnectionState.Closed)
                        dbConnection.Open();

                    var reader = sqlCommand.ExecuteReader();
                    lstStudentRecords = GetStudentRecords(reader);
                }
            }
            return lstStudentRecords; 
        }

        /// <summary>
        /// Adds SQLDependency for change notification and passes the information to Student Hub for broadcasting
        /// </summary>
        /// <param name="sqlCommand"></param>
        private static void AddSQLDependency(SqlCommand sqlCommand)
        {
            sqlCommand.Notification = null;

            var dependency = new SqlDependency(sqlCommand);

            dependency.OnChange += (sender, sqlNotificationEvents) =>
            {
                if (sqlNotificationEvents.Type == SqlNotificationType.Change)
                {
                    StudentHub.SendUptodateInformation(sqlNotificationEvents.Info.ToString());
                }
            };
        }

        /// <summary>
        /// Fills the Student Records
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static List<Student> GetStudentRecords(SqlDataReader reader)
        {
            var lstStudentRecords = new List<Student>();
            var dt = new DataTable();
            dt.Load(reader);
            dt
                .AsEnumerable()
                .ToList()
                .ForEach
                (
                    i => lstStudentRecords.Add(new Student()
                    {
                        StudentID = (int)i["StudentID"]
                            , StudentName = (string)i["StudentName"]
                            , DOB = Convert.ToDateTime(i["DOB"])
                            , Weight = (int)i["Weight"]
                    })
                );
            return lstStudentRecords;
        }       
    }
}

The GetStudentRecords() method will fetch the student records from the database. We have followed ADO.net approach, however, an ORM approach is doable. In the AddSQLDependency, we are adding the SQLDependency. The OnChangeEventHandler occurs when a notification is received for any of the commands associated with System.Data.SqlClient.SqlDependency object. As soon as a change in the database occurs (like Insert/Update/Delete/Truncate), the OnChangeEventHandler fires up and it sends the information to the SendUptodateInformation method of StudentHub.

dependency.OnChange += (sender, sqlNotificationEvents) =>
{
    if (sqlNotificationEvents.Type == SqlNotificationType.Change)
    {
        StudentHub.SendUptodateInformation(sqlNotificationEvents.Info.ToString());
    }
};

Next visit the StudentHub.cs file

using Microsoft.AspNet.SignalR;
using Microsoft.AspNet.SignalR.Hubs;

namespace SignalRInstantDbChangesDemo.Hubs
{
    public class StudentHub : Hub
    {
        [HubMethodName("sendUptodateInformation")]
        public static void SendUptodateInformation(string action)
        {
            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<StudentHub>();

	    // the updateStudentInformation method will update the connected client about any recent changes in the server data
            context.Clients.All.updateStudentInformation(action);
        }
    }
}

The StudentHub class is used for the communication for perstistent connections between client and server (hub). It makes Remote Procedure Calls (RPCs) from server to connected clients and from clients to the server. The method updateStudentInformation will update the connected client(s) about any recent changes in the server data.

The Models folder contains the information about the Student Model and the services it provides.

Student.cs contains the Student Model information.

public class Student
{
    public int StudentID { get; set; }

    public string StudentName { get; set; }

    public DateTime DOB { get; set; }

    public int Weight { get; set; }
}

The IStudentService interface conatins the services

public interface IStudentService
{
    List<Student> GetStudentDetails();
}

And the concrete implementation is found in StudentService.cs file

public class StudentService : IStudentService
{        
    public List<Student> GetStudentDetails()
    {
        return StudentRepository.GetStudentRecords();
    }
}

The HomeController looks as under

using SignalRInstantDbChangesDemo.Models;
using System.Web.Mvc;

namespace SignalRDbUpdates.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        } 

        public ActionResult SendStudentNotification()
        {
            IStudentService studentService = new StudentService();           
            return PartialView("_StudentList", studentService.GetStudentDetails());
        }
    }
}

We are displaying teh records in a partial view _StudentList

The Global.asax.cs file is of vital importance since here we are starting and stopping the SqlDependency

using System.Configuration;
using System.Data.SqlClient;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;

namespace SignalRDbUpdates
{
    public class MvcApplication : System.Web.HttpApplication
    {
        string connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
            GlobalConfiguration.Configure(WebApiConfig.Register);

            //Start SqlDependency with application initialization
            SqlDependency.Start(connString);
        }

        protected void Application_End()
        {
            //Stop SqlDependency
            SqlDependency.Stop(connString);
        }
    }
}

The SqlDependency.Start(string connectionString) method starts the listener for receiving dependency change notifications from the instance of SQL Server specified by the connection string.

The SqlDependency.Stop(string connectionString) method stops the listener for a connection specified in System.Data.SqlClient.SqlDependency.Start call.

Next we need to register the SignalR at startup class. In the Startup.cs file, we find the Startup class is decorated with

[assembly: OwinStartupAttribute(typeof(SignalRDbUpdates.Startup))]

This indicates that the class is implemented as a function that receives a Microsoft.Owin.IOwinContext instance. When the server receives an HTTP request, the OWIN pipeline invokes the middleware. The middleware sets the content type for the response and writes the response body.

Now, inside the Configuration function, we need to map SignalR hubs to the app builder pipeline at "/signalr". This can be done using the below way

using Microsoft.Owin;
using Owin;

[assembly: OwinStartupAttribute(typeof(SignalRInstantDbChangesDemo.Startup))]
namespace SignalRInstantDbChangesDemo
{
    public partial class Startup
    {
        public void Configuration(IAppBuilder app)
        {           
            app.MapSignalR();   
        }
    }
}

Now let's come to the View part. In Index.cshtml, we have the below code

@{
    ViewBag.Title = "Instant Database Changes Using SignalR Demo-RNA Team";
}
<div class="row">
	<h1 style="color: green">Instant Database Changes Using SignalR</h1>
    <div class="col-md-12">
        <div id="divStudent"></div>
    </div>
</div>
@section Scripts{
    <script src="/Scripts/jquery.signalR-2.1.1.js"></script>
    <!--Reference the autogenerated SignalR hub script. -->
    <script src="/signalr/hubs"></script>
    <script type="text/javascript">
        $(function () {
            // Create a proxy to signalr hub on web server. It reference the hub.
            var notifications = $.connection.studentHub;           
            
            // Notify to client with the recent updates from hub that broadcast messages.
            notifications.client.updateStudentInformation = function (serverResponse) {
                alert('changes triggered by ' + serverResponse + ' operation');
                getStudentInformation()

            };
            
            // Connect to signalr hub
            $.connection.hub.start().done(function () {               
                getStudentInformation();
            }).fail(function (error) {
                alert(error);
            });
        });


        function getStudentInformation() {
            var model = $('#divStudent');
            $.ajax({
                url: '/home/SendStudentNotification',
                contentType: 'application/html ; charset:utf-8',
                type: 'GET',
                dataType: 'html'
            }).success(function (result) {
                model.empty().append(result);
            }).error(function () {

            });
        }
    </script>
}

The code snippet

 $(function () 
 {
    // Create a proxy to signalr hub on web server. It reference the hub.
    var notifications = $.connection.studentHub;           
    
    // Notify to client with the recent updates from hub that broadcast messages.
    notifications.client.updateStudentInformation = function (serverResponse) {
        alert('changes triggered by ' + serverResponse + ' operation');
        getStudentInformation()

    };
    
    // Connect to signalr hub
    $.connection.hub.start().done(function () {               
        getStudentInformation();
    }).fail(function (error) {
        alert(error);
    });
});

indicates that, first we are creating a proxy to SignalR Hub on web server. Once done, then we need to establish a connection to the Hub. The server will notify the clients with the recent updates of the records through the updateStudentInformation method. Additionally, we are trapping the server response as for which event (Insert/Update/Delete/Truncate), the change has been triggered.

Next create a partial view _StudentList.cshtml that will display the Student Records.

@model IEnumerable<SignalRInstantDbChangesDemo.Models.Student>

<table class="table">
    <tr>
        <th>@Html.DisplayNameFor(model => model.StudentID)</th>
        <th>
            @Html.DisplayNameFor(model => model.StudentName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.DOB)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Weight)
        </th>
        
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.StudentID)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.StudentName)
        </td>
        <th>
            @Html.DisplayFor(modelItem => item.DOB)
        </th>
        <td>
            @Html.DisplayFor(modelItem => item.Weight)
        </td>
        
    </tr>
}

</table>

So now, we are done. Let us run the application and initially it looks as under

Now let us execute the below Insert command

--Insert Script
INSERT INTO [dbo].[Student]
           ([StudentName]
           ,[DOB]
           ,[Weight])
     VALUES
           ('Niladri Biswas'
           ,GETDATE()
           ,50)

An immediate notification occured in the Application

The message shows "changes triggered by Insert operation"

Clicking on the "OK" button we receive

Now let us execute another insert command

INSERT INTO [dbo].[Student]
           ([StudentName]
           ,[DOB]
           ,[Weight])
     VALUES
           ('Babai'
           ,GETDATE()
           ,68)

And we receive the same behaviour

Now let us execute update command

UPDATE [dbo].[Student]
   SET [Weight] = 25
 WHERE [StudentName] = 'Babai'

An immediate notification occured in the Application

The message shows "changes triggered by Update operation"

Clicking on the "OK" button we receive

Now let us execute delete command

DELETE FROM [dbo].[Student]
WHERE [StudentName] = 'Niladri Biswas'

An immediate notification occured in the Application

The message shows "changes triggered by Delete operation"

Clicking on the "OK" button we receive

Finally, execute Truncate Command and the immediate notification

The message shows "changes triggered by Truncate operation"

Clicking on the "OK" button we receive

Reference

1. Tutorial: Getting Started with SignalR 2
2. Introduction to SignalR
3. CRUD using Code First Approach of Entity Framework (EF)

Conclusion

Hope this will be helpful to understand the "Real Time" notification through instant Database Changes using SignalR. Thanks for reading. Zipped file attached.