SQL Server Real-Time update on Record Change with SignalR and SQLDependency with AngularJS.

The Sample application create to understand the concept of SignalR and SQLDependency. 

The Code

Let's assume a SQL Server database table containing employee details modified constantly:

Step 1:Create Table, Procedure and have a look at the project structure.

Table:

CREATE TABLE [dbo].[Employee](

       [EmpId] [int] IDENTITY(1,1) NOT NULL,

       [EName] [nvarchar](50) NULL,

       [DeptNo] [nvarchar](50) NULL

) ON [PRIMARY]

Procedure:

CREATE PROC p_GetEmployee

as

SELECT [EmpId]

      ,[EName]

      ,[DeptNo]

FROM [dbo].[Employee]

Step 2: Next, we install the NuGet package as shown in image:

-----------------------------------------

Step 4: To create a custom hub class, used from the SignalR infrastructure as shown:

-----------------------------------------

using System.Collections.Generic;

using Microsoft.AspNet.SignalR;

using Microsoft.AspNet.SignalR.Hubs;

namespace DemoSignalRWithSQLDependency

{

    [HubName("employeeHub")]//the name through which in js file we init and call hub start fuction. 

    public class EmployeeHub : Hub

    {

        private readonly Employee _employee;

        public EmployeeHub() : this(Employee.Instance)

        {

        }

        public EmployeeHub(Employee stockTicker)

        {

            _employee = stockTicker;

        }

        [HubMethodName("getAllEmployee")]

        public IEnumerable<Emp> GetAllEmployee()

        {

            return _employee.GetEmployee();

        }      

    }

}

Step 3: To mapping the Hubs connection

-----------------------------------------

To enable SignalR in your application, create a class called Startup with the following code:

using Microsoft.Owin;

using Owin;

using DemoSignalRWithSQLDependency;

[assembly: OwinStartup(typeof(Startup))]

namespace DemoSignalRWithSQLDependency

{

    public class Startup

    {

        public void Configuration(IAppBuilder app)

        {

            app.MapSignalR();

        }

    }

}

Step 5: create Employee class in which we write the business logic and sqlConnection

--------------------------------------------------

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using Microsoft.AspNet.SignalR;

using Microsoft.AspNet.SignalR.Hubs;

namespace DemoSignalRWithSQLDependency

{

    public class Employee

    {

        private readonly static Lazy<Employee> _instance = new Lazy<Employee>(() => new Employee(GlobalHost.ConnectionManager.GetHubContext<EmployeeHub>().Clients));

        static string _con = ConfigurationManager.ConnectionStrings["sqlCon"].ToString();

        SqlConnection sqlcon = null;

        public Employee(IHubConnectionContext<dynamic> clients)

        {

            Clients = clients;

            SqlDependency.Stop(_con);

            SqlDependency.Start(_con);

            sqlcon = new SqlConnection(_con);

        }

        private IHubConnectionContext<dynamic> Clients

        {

            get;

            set;

        }

        public static Employee Instance

        {

            get

            {

                return _instance.Value;

            }

        }

        private void BroadcastEmployee(List<Emp> employee)

        {

            Clients.All.updateEmployee(employee);

        }

       

        void OnChange(object sender, SqlNotificationEventArgs e)

        {

            SqlDependency dependency = sender as SqlDependency;

            dependency.OnChange -= OnChange;

            // Fire the event

            switch (e.Info.ToString())

            {

                case "Update":

                    {

                        if (e.Type.ToString() == "Change")

                        {

                            BroadcastEmployee(GetEmployee());

                        }

                        break;

                    }

                case "Insert":

                    {

                        if (e.Type.ToString() == "Change")

                        {

                            BroadcastEmployee(GetEmployee());

                        }

                        break;

                    }

                case "Delete":

                    {

                        if (e.Type.ToString() == "Change")

                        {

                            BroadcastEmployee(GetEmployee());

                        }

                        break;

                    }

            }

        }

        public List<Emp> GetEmployee()

        {

            DataTable dt = new DataTable();

            List<Emp> EmpModel = new List<Emp>();

            try

            {

                SqlCommand cmd = new SqlCommand("p_GetEmployee", sqlcon);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Notification = null;

                SqlDependency.Start(_con);

                SqlDependency dependency = new SqlDependency(cmd);

                dependency.OnChange += new OnChangeEventHandler(OnChange);

                if (sqlcon.State == ConnectionState.Closed)

                    sqlcon.Open();

                using (var sqlDataReader = cmd.ExecuteReader())

                {

                    while (sqlDataReader.Read())

                    {

                        var EmpId = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal("EmpId"));

                        var EName = sqlDataReader.GetString(sqlDataReader.GetOrdinal("EName"));

                        var DeptNo = sqlDataReader.GetString(sqlDataReader.GetOrdinal("DeptNo"));

                        EmpModel.Add(new Emp { EmpId = EmpId, EName = EName, DeptNo = DeptNo });

                     

                    }

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return EmpModel;

        }

        ~Employee()

        {

            SqlDependency.Stop(_con);

        }

    }

      

   //We are going to map those table columns with the following model:

    public class Emp

    {

        public int EmpId { get; set; }

        public string EName { get; set; }

        public string DeptNo { get; set; }

    }

}

Step 6: my app.js file code for the SignalR.

-------------------------------------------------------------------

var mainApp = angular.module("mainApp", []);

mainApp.controller('EmpCtrl', function ($scope, $http) {

    $scope.Title = "Employee Details";

    $scope.Employees = [];

    ///////////////////////////Code for SignalR Hub////////////////////////

    var hub = $.connection.employeeHub;

    function init() {

        debugger;

        return hub.server.getAllEmployee().done(function (emp) {

            //load employee details first time.

            $scope.$apply(function () {

                $scope.Employees = JSON.parse(JSON.stringify(emp));

            });

          

        });

    }

    $.extend(hub.client, {

        updateEmployee: function (emp) {

            //call when change on table done.

            debugger;

            $scope.$apply(function () {

                $scope.Employees = JSON.parse(JSON.stringify(emp));

            });

        }

    });

    // Start the connection

    $.connection.hub.start().then(init);

    ///////////////////// End SignalR Hub//////////////////////////////

});

Step 6: And finaly the HTML page code.

-------------------------------------------------

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

    <title></title>

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">

    <script src="Scripts/angular.min.js"></script>

</head>

<body ng-app="mainApp" ng-controller="EmpCtrl">

    <h1>SqlTableDependencly with SignalR</h1>

    <div class="col-sm-6">      

        <table class="table table-bordered table-hover table-striped">

            <tr>

                <th>EmpId

                </th>

                <th>EName

                </th>

                <th>DeptNo

                </th>

            </tr>

            <tbody>

                <tr ng-repeat="item in Employees">

                    <td>{{item.EmpId}}</td>

                    <td>{{item.EName}}</td>

                    <td>{{item.DeptNo}}</td>

                </tr>

            </tbody>

        </table>

    </div>

    <script src="Scripts/jquery-1.9.1.min.js"></script>

    <script src="Scripts/bootstrap.min.js"></script>

    <script src="Scripts/jquery.signalR-2.2.2.js"></script>

    <script src="../signalr/hubs"></script>

    <script src="Scripts/app.js"></script>

</body>

</html>