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
<!--
<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
{
}
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)
{
NotificationRepository objRepository = new NotificationRepository();
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)
{
NotificationRepository objRepository = new NotificationRepository();
objRepository.AddNotification(message, user);
}
public override System.Threading.Tasks.Task OnConnected()
{
NotificationRepository objRepository = new NotificationRepository();
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()
{
string connectionString = ConfigurationManager.ConnectionStrings["NotificationConnection"].ConnectionString;
string commandText = @"
Select
dbo.NotificationList.ID,
dbo.NotificationList.Text,
dbo.NotificationList.UserID,
dbo.NotificationList.CreatedDate
From
dbo.NotificationList
";
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);
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)
{
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)
{
context.Clients.User("<DomainName>" + item.UserID).addLatestNotification(item);
}
}
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 () {
var notification = $.connection.notificationHub;
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>");
}
}
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>");
}
$.connection.hub.start().done(function () {
$("#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