Click here to Skip to main content
Click here to Skip to main content

MVC5, JQuery Mobile & Real-Time Database Notifications

, 14 Jan 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
MVC5 - JQM - SignalR2.0.1 - SqlDependency - Sql Server 2008R2

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

  1. Visual Studio 2013 Express
  2. Sql Server 2008 R2 Express
  3. MVC 5 Nuget
  4. SqlDependency MSDN
  5. SignalR 2.0.1 Nuget
  6. Jquery 2.0.3 Nuget
  7. JQuery Mobile Nuget or Latest
  8. Opera Emulator (windows)
  9. jPlot
  10. 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();
        }
        
        /// <summary>
        /// Gets the employee list.
        /// </summary>
        /// <param name="startDate">The start date.</param>
        /// <param name="endDate">The end date.</param>
        /// <returns></returns>
        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>>();
 
            // the connection string to your database          
            string connString = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString;
 
            // SqlDependency.Stop(connString);
            SqlDependency.Start(connString);
 
            string proc = ConfigurationManager.AppSettings[SelectDefectsSproc];            
 
            //first we need to check that the current user has the proper permissions, otherwise display the error 
            if (!CheckUserPermissions()) return null;            
            
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
               
				using (SqlCommand sqlCmd = new SqlCommand())
                {
                    sqlCmd.Connection = sqlConn;
                    sqlCmd.Connection.Open();
 
                    //tell our command object what to execute
                    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);                            
                        }
                    }
 
                    // get the GroupBy bug stats
                    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);                
            }
        }       
 
        /// <summary>
        /// Checks the user permissions.
        /// </summary>
        /// <returns></returns>
        public bool CheckUserPermissions()
        {
            try
            {
                SqlClientPermission permissions = new SqlClientPermission(PermissionState.Unrestricted);
                permissions.Demand(); //if we cannot Demand() it will throw an exception if the current user doesn't have the proper permissions
                return true;
            }
            catch { return false; }
        }
 
        /// <summary>
        /// Handles the OnDataChangedDelegate event of the dependency control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Data.SqlClient.SqlNotificationEventArgs"/> instance containing the event data.</param>
        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);     
 
            //sql notification will have been used up at this stage - will be rebined later in code
            SqlDependency dependency = sender as SqlDependency;
            dependency.OnChange -= new OnChangeEventHandler(dependency_OnDataChangedDelegate);          
        }
 
        /// <summary>
        /// Serializes the object.
        /// </summary>
        /// <param name="pObject">The p object.</param>
        /// <returns></returns>
        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()
        {
            // check if application cache has previously been populated
            if (String.IsNullOrEmpty((HttpRuntime.Cache["Bugs"] as string))) // first time in
            {
                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")
 
    <!--Custom scripts\styles-->
    @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
    {
        // For more information on bundling, visit http://go.microsoft.com/fwlink/?LinkId=301862
        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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bert O Neill
Architect
Ireland Ireland
No Biography provided

Comments and Discussions

 
QuestionLow performance to high number of records PinmemberEguerrero12-Jun-14 5:47 
QuestionDatabase Pinmembershirishbatule14-Mar-14 22:32 
GeneralMy vote of 5 PinmemberStephenWilson12816-Jan-14 0:33 
QuestionSQL Server Service Broker for current databse is not enabled Pinmemberkarsten_kunz14-Jan-14 21:41 
AnswerRe: SQL Server Service Broker for current databse is not enabled PinmemberBert O Neill14-Jan-14 23:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 14 Jan 2014
Article Copyright 2014 by Bert O Neill
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid