SignalR ,the push messaging library by Microsoft makes it magically easy to add real-time response to your web application.
A very typical use of push messaging is when you want to notify the user when some data has changed in your DB.
We will,in this post, see how we can achieve this easily by using SignalR in tandem with the Sql Dependency feature of SQL Server. It is truly a match made in heaven.
I will be working in an ASP.Net MVC application here but it can be easily modifed for ASP.Net web forms or Web-API application.
DB is SQL Server 2008R2 Express.
Entity Framework is used for data access.
1. Adding SignalR
Download and install the nuget package Microsoft.AspNet.SignalR.
2. Enabling Notification service
Run the command in SQL Server :
ALTER DATABASE <yourdbname> SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;
This will enable the broker for your current working account.
3. Database
currently has one table “Messages”
Now on to the Code:
4. Enabling SignalR in Application
In your OWIN startup
</p>
using Microsoft.Owin;
using Owin;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
[assembly: OwinStartup(typeof(SignalRTest.StartUp))]
namespace SignalRTest
{
public class StartUp
{
public void Configuration(IAppBuilder app)
{
app.MapSignalR();
}
}
}
5. Readying application to receive notification
First step is to build a query notification link between our application and SQL Server Instance.
For this we will need to enable the SqlDependency in Global.asax
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;
namespace SignalRTest
{
public class WebApiApplication : System.Web.HttpApplication
{
//connection string of DB
string connString = @"Server=.\sqlexpress;Database=testDB;Integrated Security=SSPI";
protected void Application_Start()
{
//regular MVC stuff
AreaRegistration.RegisterAllAreas();
WebApiConfig.Register(GlobalConfiguration.Configuration);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
//Start SqlDependency with application initialization
SqlDependency.Start(connString);
}
protected void Application_End()
{
//Free the dependency
SqlDependency.Stop(connString);
}
}
}
next we need to inform SQL server about the specific records we want to be notified about.
This would be a simple SQL query. SQL Server will shoot a notification any time result of this query changes.
We do this in the class “NewMessageNotifier”
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace SignalRTest
{
#region Delegate
public delegate void ResultChangedEventHandler(object sender, SqlNotificationEventArgs e);
#endregion
public class NewMessageNotifier
{
#region Fields
public event ResultChangedEventHandler NewMessage;
string _connString;
string _selectQuery;
#endregion
internal NewMessageNotifier(string connString,string selectQuery)
{
_connString = connString;
_selectQuery = selectQuery;
RegisterForNotifications();
}
private void RegisterForNotifications()
{
using (var connection = new SqlConnection(_connString))
{
using (SqlCommand command = new SqlCommand(_selectQuery, connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
}
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (NewMessage != null)
NewMessage(sender, e);
//subscribe again to notifier
RegisterForNotifications();
}
}
}
We instantiate the above class by passing in the required query and connection string to DB.On every record change the event dependency_OnChange is fired.Inside this we raise an other event called “NewMessage”. Anyone subscribed to “NewMessage” will be notified.
before looking into how we subscribe to “NewMessage” let us first have a look at the SignalR hub class which performs the task of dispatching messages to client.
6. SignalR Hub
using Microsoft.AspNet.SignalR;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace SignalRTest
{
public class MessageSender : Hub
{
public void DispatchToClient(IEnumerable<string> messages)
{
foreach (string message in messages)
Clients.All.broadcastMessage(message);
}
public MessageSender()
{
Action<IEnumerable<string>> dispatcher = (messages) => { DispatchToClient(messages); };
//We create a singleton instance of PushMessaging
PushMessaging.GetInstance(dispatcher);
}
}
}
This class is instantiated internally by SignalR library when ever a client connects.
Now, as you might have guessed, we need to make DB change notifier and SignalR message dispatcher talk to each other.
We do this in the class “PushMessaging” .We have created an instance of which above.
since the hub is re-instantiated on every new request from client we have created a singleton instance of “PushMessaging”.
Now lets have a look at code of “PushMessaging” where the magic happens
7. Push Messaging
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SignalRTest
{
// This class recieves the fresh notifications from DB as forwarded by "NewMessageNotifier".
// It then asks "MessageSender" to disptach messages to client
class PushMessaging
{
static PushMessaging _instance = null;
NewMessageNotifier _newMessageNotifier;
Action<IEnumerable<string>> _dispatcher;
string _connString;
string _selectQuery;
public static PushMessaging GetInstance(Action<IEnumerable<string>> dispatcher)
{
if (_instance == null)
_instance = new PushMessaging(dispatcher);
return _instance;
}
private PushMessaging(Action<IEnumerable<string>> dispatcher)
{
_dispatcher = dispatcher;
_connString = @"Server=.\sqlexpress;Database=testDB;Integrated Security=SSPI";
_selectQuery = @"SELECT [MessageID],[UserID],[MessageText],[IsSent] FROM [dbo].[Messages]";
_newMessageNotifier = new NewMessageNotifier(_connString, _selectQuery);
_newMessageNotifier.NewMessage += NewMessageRecieved;
}
internal void NewMessageRecieved(object sender, SqlNotificationEventArgs e)
{
IEnumerable<Message> newMessages = FetchMessagesFromDb();
_dispatcher(newMessages.Select(lm => lm.MessageText));
using (testDBEntities db = new testDBEntities())
{
//Mark all dispatched messages as sent
newMessages.ToList().ForEach(lm => { db.Messages.Attach(lm); lm.IsSent = true; });
db.SaveChanges();
}
}
private IEnumerable<Message> FetchMessagesFromDb()
{
using (testDBEntities db = new testDBEntities())
{
return db.Messages.Where(lm => lm.IsSent == false).ToList();
}
}
}
}
This class instantiates “NewMessageNotifier” and subscribes to its “NewMessage” event.
When “NewMessage” triggers,it fires the Dispatcher method of “MessageSender” via Action delegate.
Lastly the MVC view
8. View
@{
ViewBag.Title = "SignalRClient";
}
<div style="padding:10px 0px 10px 10px">
New Messages:
<ul id="messagecontainer">
</ul>
</div>
@section scripts {
<script src="~/Scripts/jquery.signalR-2.0.3.min.js"></script>
<script src="~/signalr/hubs"></script>
<script>
$(function () {
// Reference the auto-generated proxy for the hub.
var clientHub = $.connection.messageSender;
// Create a function that the hub can call back to display messages.
clientHub.client.broadcastMessage = function (message) {
// Add the message to page.
$('#messagecontainer').append('<li>' + message + '</li>');
};
$.connection.hub.start().done();
});
</script>
}
This completes and we can see that any change in DB is reflected in real time on view.
Before closing, few important points :
1. Query notification service is particular about its queries. See here for details http://msdn.microsoft.com/library/ms181122.aspx
2. If you need to grant query notification permission to another account :
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO “domain\user”
and that’s it.Hope you find this post useful.
If you have any suggestion or questions then please pass on to me.
Addendum :
Here is additional set of information for anyone not acquainted with Entity Framework,the DB access layer I have used here.
Microsoft has an excellent step-by-step tutorial which explains implementing EF in a new MVC application.Whole tutorial is great but the below two steps should be sufficient to work with my post :
http://www.asp.net/mvc/tutorials/mvc-5/database-first-development/setting-up-database
http://www.asp.net/mvc/tutorials/mvc-5/database-first-development/creating-the-web-application
If you are following all steps in this post then make sure to name your SQL Server DB as “testDB”and rest should fall in place.
Hope it helps and you have one more addition to your skill set 🙂
The Anonymous Type 