In one of my previous posts, I talked about using SQLDependency as a mechanism for detecting triggers on certain SQL queries. In this post, I’ll talk about how to use this mechanism to display real-time data in web applications using SignalR.
Before we start, make sure you have created a new ASP.NET MVC 5 project in Visual Studio. Although strictly not necessary, verify whether you have a working database and database connection, as well as the Entity Framework context and models classes. Once you have done that, two distinct set of actions must be taken in order to display realtime data from the database on a web application:
- Create SQL trigger and attach it to .NET code with SQLDependency
- Notify front end of any events from this trigger and update the UI with SignalR
Creating C# trigger on sQL database with SQL Dependency
Most of what needs to happen in this part is documented in this post. In your ASP.NET MVC project, choose a random controller action and paste following code:
Nothing but one item should seem new if you’ve read my previous article on this topic. The databasechangelistener class is reusable across all projects and contains no business logic. The event handler though contains 1 very interesting line of code:
ChatHub.SendMessages();
This notifies the SignalR mechanism that something has happened and that the front end should take action. Anything that happens after this has nothing to do with SQLDependency any more: each time something happens to the specified query, it will fire an event and it will notify the Hub of a certain event.
Notify and Modify user interface with SignalR
Here comes the interesting part: realtime display of information on web applications. On MSDN, there is a nice article that describes the different components required to make this all happen. In a nutshell, we can differentiate between following elements:
- Installation SignalR
- Configuration of SignalR in the application
- Definition of the Hub and its different actions in C#
- Connection to the mechanism in the front end using jQuery
- Define custom behavior in the event of triggers
I’ll just skip right away to the latter two items, as the others ones have been documented by many others before me. In the trigger event handler, there was a static method in the Hubclass which is called. The following code shows its implementation:
As you can see, this code calls the SignalR assembly and calls the (anonymous) updateMessages method. This method definition will have to be met in the jQuery code. In the Razor view, I have the following code:
Please note following line of code:
notifications.client.updateMessages
This defines some sort of delegate that will be executed as soon as its called by some piece of code. In this case, this method is called each time there has been a change on the database level. The JavaScript function getAllMessages() implements the delegate, and calls a controller action that has the following code:
Now build your project and hit F5. If everything has worked, you’ll see that changes on the database level will have immediate effect on the user interface.
