Real Time Notifications using SignalR and SQL Dependency

Introduction

There was a requirement in one of the projects that I had worked with, which demanded Notifications being shown to user in real time. SignalR seemed like the immediate choice as it provides the framework for server side code to push data to clients who are connected in real time. But the notifications can be sent through external systems to the database directly. In that case there needs to be a mechanism to get the newly inserted record and send it to the connected client. This article discusses about how to connect all these pieces together and build a "real" time notification solution.

Background

Basic Introduction about SignalR

SignalR is a .NET library that simplifies the process of adding real time functionality to web applications. It provides us the framework for server side code to invoke client side method rather than server wait for client to request data from server.

It provides us the API for creating server to client (RPC) that calls JavaScript methods from server side .NET code. It also provides us with methods for connect, disconnect events, send to all connected clients, send to specific client etc.

For More information regarding SignalR - http://www.asp.net/signalr/overview/getting-started

SQL Dependency

SQL dependency is a class provided by the .NET Framework that is built on the service broker infrastructure which allows application to be notified when a data has changed in the database (Microsoft SQL Server). Microsoft SQL Server allows .NET Framework applications to send a command (SQL Command) to SQL Server and request notification if executing the command would produce a different result.

SQL server allows .NET Framework applications to set up for SELECT and EXECUTE statements.

Let’s Begin

SQL Server

Start with Creating a Table in a Database.

** User ID – column contains the user id of the user to which the notification needs to be sent. Run the following script on the database where you had created the table, so that SQL server would start notifying the .NET application which subscribes to changes on the table ALTER DATABASE [dbname] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ; This is all that is required from the SQL Server side.

ASP.NET MVC Web Application

Let us start now creating a web application. Create an ASP.NET MVC 4 basic application.

Create a "HomeController" by right clicking on the Controllers folder and selecting add new controller

Right click on the Views folder and create a new folder named "Home"

Right Click on the newly created "Home" folder and Select Add > View and Name the View as "index" and Click on "Add". Go to the Package Manager console and run the following command, which will bring the necessary libraries and the JavaScript required for SignalR

PM> Install-Package Microsoft.AspNet.SignalR

Go to the Layout.cshtml and remove all the "@scripts.render" and replace them with the following inside the Head section

<link href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://code.jquery.com/jquery-1.11.2.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<script src="~/Scripts/jquery.signalR-2.2.0.min.js"></script>

Now open the index.cshtml inside the Home folder that we have created. Add this which would be the proxy created by the server to call the client

<!--Reference the autogenerated SignalR hub script. -->

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

Create the required HTML for displaying the Notification List, Count and a form to send notifications. Add this code in the index.cshtml added within the Home Folder View

<div style="width: 70%; padding: 20px">
    <div class="panel panel-primary">
        <div class="panel-heading">

    <! – To show notification count-->
            <div style="float: left" class="panel-title">Notifications</div>
            <div style="float: right" class="badge" id="cntNotifications"></div>
            <div style="clear: both"></div>


        </div>
        <div class="panel-body">
      <! – To show All the notifications-->
            <table class="table table-striped table-hover ">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Text</th>
                        <th>Created Date</th>
                    </tr>
                </thead>

                <tbody id="notificationTab">

                </tbody>
            </table>
        </div>
    </div>

    <! – Add panel notification to send notification, Make sure that user enters the user id of the domain they are logged into --> 
    <div class="panel panel-primary">
        <div class="panel-heading">
            <h3 class="panel-title">Create Notifications</h3>
        </div>
        <div class="panel-body">
            <div class="form-group">
                <label class="control-label" for="focusedInput">Notification Text</label>
                <input class="form-control" id="text" type="text" value="">
            </div>
            <div class="form-group">
                <label class="control-label" for="focusedInput">Send To</label>
                <input class="form-control" id="userName" type="text" value="">
            </div>
            <a id="btnSend" style="cursor: pointer" class="btn btn-primary">Send Notification</a>
        </div>
    </div>
</div>

Let us go back to the server side code now and create code for fetching notifications from database and to add notifications to the database. Right-click on the Project file add a new folder "Repository and add a class file "NotificationRespository.cs". The following code snippet needs to be present in the file. Add a new item and select Data > ADO.NET Entity Data Model and give it a name.

Select "Generate from the database" in the next section.

Provide the Server name, Database name and complete the wizard. Replace "NotificationEntities" with the name of the entity that you had given.

public void AddNotification(string Text, string UserName)
{
    using (NotificationEntities ent = new NotificationEntities())
    {
        NotificationList obj = new NotificationList();
        obj.Text = Text;
        obj.UserID = UserName;
        obj.CreatedDate = DateTime.Now.ToUniversalTime();
        ent.NotificationLists.Add(obj);
        ent.SaveChanges();
    }
}

public List<NotificationList> GetNotifications(string userName)
{

    using (NotificationEntities ent = new NotificationEntities())
    {
        return ent.NotificationLists.Where(e => e.UserID == userName).ToList();
    }
}

public List<NotificationList> GetLatestNotifications(DateTime dt)
{

    using (NotificationEntities ent = new NotificationEntities())
    {
        if (dt == DateTime.MinValue)
        {
            return ent.NotificationLists.ToList();
        }
        else
        {
            DateTime dtUTC = dt.ToUniversalTime();
            return ent.NotificationLists.Where(e => e.CreatedDate > dtUTC).ToList();
        }
    }
}

The code is pretty straight forward to understand. So we have created three methods. AddNotification to add Notification to the db, GetNotifications(username) to get the notifications for the user name.

Finally, the GetLatestNotification method (date time) which selects the list of the notification since the last run, to get the added record after every run, which will be called on the SQL dependency event.

Now, let us create a SignalR Hub, which is the server side code which will invoke the client side JavaScript method to show notification.

Right-click on the project file and add a new folder "Hubs". Create a class file called "Notification.cs". Make sure that the class inherits from Hub class (SignalR class)

public class NotificationHub: Hub
{
   // Code 

}

Create a method "SendNotification" which the clients will invoke to create a notification on to the database. The clients should pass two variables, username – to whom the notification needs to be sent,

public class NotificationHub: Hub
{
   
        public void SendNotification(string message, string user)
        {
	//Create an instance of the Repository class
            NotificationRepository objRepository = new NotificationRepository();

	//Invoke the Add Notification method that we created in the repository to add the notification to the database 
            objRepository.AddNotification(message, user);          
        }

}

Now override the onConnected method to get the notifications for the logged in user and send the response back

public class NotificationHub: Hub
{
   
        public void SendNotification(string message, string user)
        {
	//Create an instance of the Repository class
            NotificationRepository objRepository = new NotificationRepository();

	//Invoke the Add Notification method that we created in the repository to add the notification to the database 
            objRepository.AddNotification(message, user);          
        }

        public override System.Threading.Tasks.Task OnConnected()
        {
	//Create an instance of the Repository class
            NotificationRepository objRepository = new NotificationRepository();
     
	//refreshNotification is the client side method which will be writing in the future section. GetLogin() is a static extensions extract just the login name scrapping the domain name 
            Clients.User(Context.User.Identity.Name).refreshNotification(objRepository.GetNotifications(Context.User.Identity.GetLogin()));

            return base.OnConnected();

        }


}

public static class Extensions
    {
        public static string GetDomain(this IIdentity identity)
        {
            string s = identity.Name;
            int stop = s.IndexOf("\\");
            return (stop > -1) ? s.Substring(0, stop) : string.Empty;
        }

        public static string GetLogin(this IIdentity identity)
        {
            string s = identity.Name;
            int stop = s.IndexOf("\\");
            return (stop > -1) ? s.Substring(stop + 1, s.Length - stop - 1) : string.Empty;
        }
    }

Now we need to wire up the table with SQL Dependency change and initiate the SignalR hub. Open the Global.asax.cs file. The sad thing with SQL dependency is that it will only raise an event saying there is a change that has happened to the table and not which record got inserted/ modified. We need to query back to the database to get the change that has happened.

Create a void method named "RegisterNotification" within the global.asax.cs file. Make sure that this RegisterNotification method is invoked in the application_start method

private void RegisterNotification()
        {
	//Get the connection string from the Web.Config file. Make sure that the key exists and it is the connection string for the Notification Database and the NotificationList Table that we created

            string connectionString = ConfigurationManager.ConnectionStrings["NotificationConnection"].ConnectionString;

	//We have selected the entire table as the command, so SQL Server executes this script and sees if there is a change in the result, raise the event
            string commandText = @"
                                    Select
                                        dbo.NotificationList.ID,
                                        dbo.NotificationList.Text,
                                        dbo.NotificationList.UserID,
                                        dbo.NotificationList.CreatedDate                                      
                                    From
                                        dbo.NotificationList                                     
                                    ";

	//Start the SQL Dependency
            SqlDependency.Start(connectionString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                using (SqlCommand command = new SqlCommand(commandText, connection))
                {
                    connection.Open();
                    var sqlDependency = new SqlDependency(command);


                    sqlDependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);

                    // NOTE: You have to execute the command, or the notification will never fire.
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                    }
                }
            }
        }

Now handle the OnChange event. Check if the event is of type "Insert" Get the latest record and send it to the Connected Client

DateTime LastRun;
     private void sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
     {

         if (e.Info == SqlNotificationInfo.Insert)
         {
 //This is how signalrHub can be accessed outside the SignalR Hub Notification.cs file
             var context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();


             NotificationRepository objRepos = new NotificationRepository();

             List<NotificationList> objList = objRepos.GetLatestNotifications(LastRun);

             LastRun = DateTime.Now.ToUniversalTime();


             foreach (var item in objList)
             {
      //replace domain name with your own domain name
                 context.Clients.User("<DomainName>" + item.UserID).addLatestNotification(item);
             }

         }
       //Call the RegisterNotification method again
         RegisterNotification();
     }

To instantiate the SignalR Hub class in this project we need to add a class. This class can be added in the same global.asax.cs file

public class Startup
 {
     public void Configuration(IAppBuilder app)
     {
         app.MapSignalR();
     }
 }

In the web.config file add a key for "owin:AppStartup" and add a value the fully qualified name of the Startup class that we had created in the previous section

<appSettings>
	…
   
    <add key="owin:AppStartup" value="Notifications.Signalr.Startup" />
  </appSettings>

Finally, to the client side to create those two methods which the server will invoke for showing the list of the notifications and send latest added one.

<script>
    $(function () {

        // Reference the auto-generated proxy for the hub.  

        var notification = $.connection.notificationHub;

 // Client side method for receiving the list of notifications on the connected event from the server
        notification.client.refreshNotification = function (data) {
            $("#notificationTab").empty();
            $("#cntNotifications").text(data.length);
            for (var i = 0; i < data.length; i++) {
                $("#notificationTab").append("<tr> <td> " + data[i].ID + "</td> <td>" + data[i].Text + "</td> <td>" + data[i].CreatedDate + "</td></tr>");
            }
        }
     
//Client side method which will be invoked from the Global.asax.cs file. 
        notification.client.addLatestNotification = function (data) {
            $("#cntNotifications").text($("#cntNotifications").text() + 1);
            $("#notificationTab").append("<tr> <td> " + data.ID + "</td> <td>" + data.Text + "</td> <td>" + data.CreatedDate + "</td></tr>");
        }

        // Start the connection.
        $.connection.hub.start().done(function () {

	//When the send button is clicked get the text and user name and send it to server. 
            $("#btnSend").click(function () {
                notification.server.sendNotification($("#text").val(), $("#userName").val());
            });

        });
    });
</script>

This completes the coding.

The following pictures depict the things that are happening in the solution on a high level