Introduction
In some my previous blogs, I would of used Microsoft’s Duplex communication to perform the call-back to the client (notably in the Silverlight environment) or I would have used WebSockets on their own to perform the client notification. But this time I wanted to use SignalR as the transport channel, to notify the client(s) of an action (create, update, delete) that was performed on a database.
Technologies
- Visual Studio 2013 Express
- Sql Server 2008 R2 Express
- MVC 5 Nuget
- SqlDependency MSDN
- SignalR 2.0.1 Nuget
- Jquery 2.0.3 Nuget
- JQuery Mobile Nuget or Latest
- Opera Emulator (windows)
- jPlot
- Toast Notifications
Scenario
I wanted to develop an end to end example that would also be a real life example (technology wise) as possible. The MVC5 project basically mimics a manager who wishes to be kept up-to-date regarding the status of bugs for a project.
Project Structure
Full Solution Structure
You can see that the project is still very much MVC oriented, but with an additional SignalR hub folder to contain the Hub server class. Other that a little configuration (edit of the Start.cs) class, there is very little to change compared to a normal MVC project.
Fig 1
JavaScript Scripts Used
There are a number of 3rd party JQuery oriented controls used with the application, namely jPlot and Toast, to display a pie chart and notification respectively. The JQuery Mobile script and style are included for the rendering style associated with mobile pages. We have a custom script "Initialise.js", is used to perform the binding to the controls, connect to the SignalR hub and receive data from the server.
Fig 2
Database Broker Setup
Attaching BugTracker Database
Before attaching the database, create a user called "GeneralUser" with a password of "Passw0rd" in your SQl Server logins. Attached the zipped database and then assign the user "GeneralUser" as an owner to this database.
Script to Create Broker & Service
Run the following script in a Sql query pane, to create a message broker\service for the BugTracker database (if you attach the database files along with this post, you do not have to run the following script).
USE BugTracker;
GO
CREATE QUEUE BugTrackerQueue;
CREATE SERVICE BugTrackerService ON QUEUE BugTrackerQueue (
[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO GeneralUser;
ALTER DATABASE BugTracker SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE BugTracker SET ENABLE_BROKER
ALTER DATABASE BugTracker SET MULTI_USER
GO
Code Explanation
SqlDependency
The C# database listener code (below) when initialized, will create a unique database broker\service instance within the database (fig xx). The GetDefectlist(); method will retrieve the defects from the database and create a listener each time the respective database table is updated, new record inserted or deleted. I am caching the defects with within an application variable, so that when a new client connects to the site, they do not have to query the database for the latest defects – this cache will be kept when a change is detected in the database as the method dependency_OnDataChangedDelegate(…) will be run – thus invalidating the cache.
public class SqlListener
{
private BugStatus bugStatus;
private Object threadSafeCode = new Object();
public SqlListener()
{
bugStatus = new BugStatus();
}
public string GetDefectList()
{
const string SelectDefectsSproc = "SelectDefectsSproc";
const string ConnectionString = "bugsDatabaseConnectionString";
this.bugStatus.BugDetails = new List<BugDetails>();
this.bugStatus.BugStatusCount = new List<Tuple<string, int>>();
string connString = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString;
SqlDependency.Start(connString);
string proc = ConfigurationManager.AppSettings[SelectDefectsSproc];
if (!CheckUserPermissions()) return null;
using (SqlConnection sqlConn = new SqlConnection(connString))
{
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.Connection = sqlConn;
sqlCmd.Connection.Open();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = proc;
sqlCmd.Notification = null;
SqlDependency dependency = new SqlDependency(sqlCmd);
dependency.OnChange += new OnChangeEventHandler(dependency_OnDataChangedDelegate);
if (sqlConn.State != ConnectionState.Open) sqlConn.Open();
using (SqlDataReader reader = sqlCmd.ExecuteReader())
{
while (reader.Read())
{
BugDetails details = new BugDetails();
details.BugId = reader.GetInt32(0);
details.Header = reader.GetString(1);
details.Created = reader.GetDateTime(2);
details.Assignee = reader.GetString(3);
details.CurrentStatus = reader.GetString(4);
this.bugStatus.BugDetails.Add(details);
}
}
var noticesGrouped = this.bugStatus.BugDetails.GroupBy(n=> n.CurrentStatus).
Select(group =>
new
{
Notice = group.Key,
Count = group.Count()
});
foreach (var item in noticesGrouped) this.bugStatus.BugStatusCount.Add(new Tuple<string, int>(item.Notice, item.Count));
}
lock (threadSafeCode)
{
HttpRuntime.Cache["Bugs"] = SerializeObjectToJson(this.bugStatus);
}
return (HttpRuntime.Cache["Bugs"] as string);
}
}
public bool CheckUserPermissions()
{
try
{
SqlClientPermission permissions = new SqlClientPermission(PermissionState.Unrestricted);
permissions.Demand();
return true;
}
catch { return false; }
}
private void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e)
{
if (e.Type != SqlNotificationType.Change) return;
var context = GlobalHost.ConnectionManager.GetHubContext<DefectsHub>();
string actionName = ((System.Data.SqlClient.SqlNotificationInfo)e.Info).ToString();
context.Clients.All.addMessage(this.GetDefectList(), actionName);
SqlDependency dependency = sender as SqlDependency;
dependency.OnChange -= new OnChangeEventHandler(dependency_OnDataChangedDelegate);
}
public String SerializeObjectToJson(Object objBugs)
{
try
{
return new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(objBugs);
}
catch (Exception) { return null; }
}
}
When the SqlDependency is created with the SqlDependency.Start(); command, a new (unique) queue and service objects are created (related to each other, fig 3) within the database. If I had of provided a name in the start command as a parameter, the queue and service would can have been called this parameter name, instead of a GUID style naming convention below.
Fig 3
SignalR Hub
There are two simple methods within the DefectHub class, one to push the newly edited database table data to the clients (all the connected clients in this case), and a method to retrieve cached data when a device connects for the first time (performance gain). You will notice that I have put a lock on the code that updates the cache, so that there are no conflicts between threads when performing the update.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.AspNet.SignalR;
using MvcJqmSqlDependencySignalR.Controller;
namespace MvcJqmSqlDependencySignalR.Controllers
{
public class DefectsHub : Hub
{
private Object threadSafeCode = new Object();
public void Send(string jsonBugs, string action)
{
Clients.All.addMessage(jsonBugs, action);
}
public void Start()
{
if (String.IsNullOrEmpty((HttpRuntime.Cache["Bugs"] as string)))
{
lock (threadSafeCode)
{
SqlListener listener = new SqlListener();
string jsonBugs = listener.GetDefectList();
HttpRuntime.Cache["Bugs"] = jsonBugs;
Clients.Caller.addMessage(jsonBugs, "Select");
listener = null;
}
}
else
{
Clients.Caller.addMessage((HttpRuntime.Cache["Bugs"] as string), "Select");
}
}
}
}
Controller
The home controller is very simple, in that it will just redirect any traffic to the view with no prior processing or passing of parameters to the view. This is a concept that is very common when using SignalR, as the server code will talk directly to the client and thus no middle processing of data is called for by the controller.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace MvcJqmSqlDependencySignalR.Controllers
{
public class HomeController : System.Web.Mvc.Controller
{
public ActionResult Index()
{
ViewBag.Title = "Defects";
return View();
}
}
}
Custom JavaScript (Initialise.js)
This custom script will perform the connection to the SignalR class on the server and create the respective bindings for the controls and notifications.
$(document).ready(function () {
// notification initialise
$.mobile.loading('show', {
text: 'Connecting to server...',
textVisible: true,
theme: 'b',
html: ""
});
// SignalR initialise
var bugs = $.connection.defectsHub;
// server entry point to client
bugs.client.addMessage = function (jsonBugs, action) {
var header = '';
switch (action) {
case "Select":
header = "Bug selection...";
break;
case "Update":
header = "Bug updation...";
break;
case "Delete":
header = "Bug deletion...";
break;
case "Insert":
header = "Bug insertion...";
break;
default:
header = "Bug status...";
}
var bugStatus = header,
toastMessageSettings = {
text: bugStatus,
sticky: false,
position: 'top-right',
type: 'success',
closeText: ''
};
var BugStatusCount = [];
var BugDetails = [];
var obj = $.parseJSON(jsonBugs);
BugStatusCount = obj.BugStatusCount;
BugDetails = obj.BugDetails;
// build up table row from array
var content = '';
$.each(BugDetails, function () {
content += "<tr> <th>" + this['BugId'] + "</th><td>" + this['Header'] + "</td><td>" + ConvertJsonDateString(this['Created']) + "</td><td>" + this['Assignee'] + "</td><td>" + this['CurrentStatus'] + "</td> </tr>";
});
$('#bugGrid tbody').html(content);
// convert json to array
data = [];
for (var prop_name in BugStatusCount) {
data.push([BugStatusCount[prop_name].Item1, BugStatusCount[prop_name].Item2])
}
// populate graph
var plot1 = jQuery.jqplot('chart1', [data],
{
title: 'Bug Report',
seriesDefaults: {
renderer: jQuery.jqplot.PieRenderer,
rendererOptions: {
showDataLabels: true
}
},
legend: { show: true, location: 'e' }
}
);
var myToast = $().toastmessage('showToast', toastMessageSettings); // display notification
};
// start SignalR
$.connection.hub.start().done(function () {
bugs.server.start();
$.mobile.loading('hide'); // hide spinner
});
// SignalR End
});
function ConvertJsonDateString(jsonDate) {
var shortDate = null;
if (jsonDate) {
var regex = /-?\d+/;
var matches = regex.exec(jsonDate);
var dt = new Date(parseInt(matches[0]));
var month = dt.getMonth() + 1;
var monthString = month > 9 ? month : '0' + month;
var day = dt.getDate();
var dayString = day > 9 ? day : '0' + day;
var year = dt.getFullYear();
var time = dt.toLocaleTimeString();
shortDate = dayString + '/' + monthString + '/' + year + ' : ' + time;
}
return shortDate;
};
View/JQuery Mobile Markup
Below is the view for the home page (Html 5 syntax). Using JQuery Mobile style, to render the web page specifically for mobile devices. I also included scripts at the bottom of the page (rendering the page quicker). The Layout page will load any scripts\styles that I do need loaded prior to rendering the html.
@{
}
<div data-role="tabs">
<div data-role="navbar">
<ul>
<li><a href="#one" data-theme="a" data-ajax="false">Graph</a></li>
<li><a href="#two" data-theme="a" data-ajax="false">Grid</a></li>
</ul>
</div>
<div id="one" class="ui-content">
<h1>Pie Chart</h1>
<div id="chart1" style="height: 250px; width: 350px;">
</div>
</div>
<div id="two" class="ui-content">
<h1>Grid Data</h1>
<table data-role="table" id="bugGrid" data-mode="columntoggle" class="ui-body-d ui-shadow table-stripe ui-responsive"
data-column-btn-theme="b" data-column-btn-text="Bug Headings..." data-column-popup-theme="a">
<thead>
<tr class="ui-bar-d">
<th>
BugID
</th>
<th>
Header
</th>
<th>
Created
</th>
<th>
<abbr title="Name">Assignee</abbr>
</th>
<th>
Status
</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
<div data-role="footer" data-position="fixed" data-tap-toggle="false" class="jqm-footer">
</div>
@section scripts {
<!--Script references. -->
<!--The jQuery library is required and is referenced by default in _Layout.cshtml. -->
<!--JQuery Plot-->
<link href="~/Scripts/jPlot/jquery.jqplot.min.css" rel="stylesheet" type="text/css" />
<script src="~/Scripts/jPlot/jquery.jqplot.min.js" type="text/javascript"></script>
<script src="~/Scripts/jPlot/jqplot.pieRenderer.min.js" type="text/javascript"></script>
<!--Toast-->
<link href="~/Scripts/Toast/css/jquery.toastmessage.css" rel="stylesheet" type="text/css" />
<script src="~/Scripts/Toast/jquery.toastmessage.js" type="text/javascript"></script>
<!--Reference the autogenerated SignalR hub script. -->
<script src="~/signalr/hubs"></script>
<!--Custom page script-->
<script src="~/Scripts/Custom/Initialise.js" type="text/javascript"></script>
}
Shared Layout Page
All that I done with the layout page, was to remove any of the menu html\razor code. I just wanted to include the main bundles and a couple of my own bundles.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - Mobile</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
@Scripts.Render("~/bundles/jquery")
<!--
@Styles.Render("~/bundles/3rdPartyCss")
@Scripts.Render("~/bundles/3rdPartyScripts")
</head>
<body>
<div class="container body-content">
@RenderBody()
</div>
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html>
Bundle Configuration
The only change here, is that I created my own custom script and styles.
using System.Web;
using System.Web.Optimization;
namespace MvcJqmSqlDependencySignalR
{
public class BundleConfig
{
public static void RegisterBundles(BundleCollection bundles)
{
bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
"~/Scripts/jQuery2.0.3/jquery-2.0.3.min.js"));
bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
"~/Scripts/modernizr-*"));
bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
"~/Scripts/bootstrap.js",
"~/Scripts/respond.js"));
bundles.Add(new StyleBundle("~/Content/css").Include(
"~/Content/bootstrap.css",
"~/Content/site.css"));
bundles.Add(new ScriptBundle("~/bundles/3rdPartyScripts").Include(
"~/Scripts/Jqm/jquery.mobile-1.4.0.min.js",
"~/Scripts/jquery.signalR-2.0.1.min.js"));
bundles.Add(new StyleBundle("~/bundles/3rdPartyCss").Include(
"~/Scripts/jquery.mobile-1.4.0.min.css"));
}
}
}
Models
BugsDetails Class
This class is just a plain old model object that is consumed by the BugStatus class.
using System;
namespace MvcJqmSqlDependencySignalR.Models
{
public class BugDetails
{
public BugDetails() { }
public int BugId { get; set; }
public string Header { get; set; }
public DateTime Created { get; set; }
public string Assignee { get; set; }
public string CurrentStatus { get; set; }
}
}
BugsStatus Class
This class will be serialised (into JSON) and returned to the client, bound to the jPlot control and a dynamic table will be built up using arrays of data.
using System;
using System.Collections.Generic;
namespace MvcJqmSqlDependencySignalR.Models
{
public class BugStatus
{
public List<Tuple<string, int>> BugStatusCount;
public List<BugDetails> BugDetails;
public BugStatus()
{
BugStatusCount = new List<Tuple<string, int>>();
BugDetails = new List<BugDetails>();
}
}
}
Application Running (Screenshots)
Open the Opera emulator from your start menu. Select any Tablet or iPhone (fig 4) that you wish to display the web page in. The majority of the devices emulated are android based. But this emulator is good for size rather than device OS – but that isn’t the issue here as we are just displaying a web application within a devices browser. Thus, testing the screen resolution for the various devices.
Fig 4
Select your device to emulate and click on the launch button to bring up the emulator (fig 5), enter the web sites URL into the browser address bar and hit enter. application and copy the URL into your clipboard – to be pasted into the device browser URL – save typing it in.
Hint: Run the application on your desktop web browser and copy the URL into your device browser URL
Fig 5
Edit the defects table by changing the status of one of the records (fig 6), thus mimicking another application making the change to the database table. This will in turn, fire a dependency event attached to our SqlListener class, which pushes the new data down to the client, which binds the data to the client controls and displays a toast notification.
Fig 6
The toast notification popping up on the client’s browser (fig 7).
Fig 7
Multiple clients connected to the site, displaying the current defect statuses. When you make a change to the database table, each client will update its controls and display the notification to the user (fig 8).
Fig 8
Changed a bug status from closed to resolved (fig 9).
Fig 9
The grid tab of the application display the defects in more details, I have two emulators open to show each tab, graph and grid (fig 10).
Fig 10
