2014-10-27

Get database change notification in asp.net using SignalR


In this tutorial, I’m going to show you how to get real time notifications in an ASP.NET web application when there is a database change. I’m going to use SignalR and SqlDependency for that.

The tools I’ll be using through out this tutorial are Microsoft Visual Studio 2013 Ultimate Update 3 and Microsoft SQL Server 2012.

Let’s create a simple ASP.NET SignalR application first. For that, I’m creating a new web application and adding the SignalR libraries to it. We can install SignalR to our web application via “Manage Nuget Packages” option in visual studio. It’s the easiest way. Right click on the project from solutions explorer and click Manage Nuget Packages… There you can type SignalR on the search box and install it to your project.

Next step is to add OWIN Startup class to the project. Like below

using System;
using System.Threading.Tasks;
using Microsoft.Owin;
using Owin;

[assembly: OwinStartup(typeof(DatabaseNotification.Startup))]

namespace DatabaseNotification
{
    public class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            app.MapSignalR();
        }
    }
}

We need a database for this web application. Let’s create a database called “Test” and a table called “DummyData”. For the simplicity, I’ve created only one column called “Message” – type nvarchar(max) in that database table.

image

Now I need to enable Broker for the database. For that, I’m going to SQL Server Management Studio and right click on the database –> Go to Properties. Then select Options. Set Broker Enable to True. Like below

image

If you don’t enable Service Broker for the database, query notifications will not be supported so you’ll get an System.InvalidOperationException exception.

Next step is to starts the listener for receiving dependency change notifications. For that, I’m adding SqlDependency.Start() method to the Application_Start() method in Global.asax file. Like below

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.SessionState;

namespace DatabaseNotification
{
    public class Global : System.Web.HttpApplication
    {
        protected void Application_Start(object sender, EventArgs e)
        {
            SqlDependency.Start(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString);
        }
    }
}

I’m passing the connection string to my database. Which looks like below in my web.config file

<connectionStrings>
  <add connectionString="Data Source=(LocalDB)\V11.0;Initial Catalog=Test;Integrated Security=True" name="TestDB" />
</connectionStrings>

Now I’ve successfully established a listener for receiving dependency change notifications from my database.

My next step is to create a hub. Right click on the project from Solution Explorer, Add –> SignalR Hub Class (v2)

Below is the code for my hub class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.AspNet.SignalR;

namespace DatabaseNotification
{
    public class NotificationsHub : Hub
    {
        public void NotifyAllClients(string msg)
        {
            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationsHub>();
            context.Clients.All.displayNotification(msg);
        }
    }
}

I have a method called NotifyAllClients() which accepts a string parameter. Inside that method, I’m calling a client side method called displayNotification() for all the connected clients.

Now my hub is there. Let’s create a web form. Below is the code behind for the web form.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DatabaseNotification
{
    public partial class Messages : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            SendNotifications();
        }

        public void SendNotifications()
        {
            string message = string.Empty;
            string conStr = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;

            using (SqlConnection connection = new SqlConnection(conStr))
            {
                string query = "SELECT [Message] FROM [dbo].[DummyData]";

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Notification = null;
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        message = reader[0].ToString();
                    }
                }
            }
            NotificationsHub nHub = new NotificationsHub();
            nHub.NotifyAllClients(message);
        }

        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                SendNotifications();
            }
        }
    }
}

Let’s take a look at what each part of the code do. First few lines of the code behind is pretty basic. I’m calling a method called SendNotifications() inside the Page_Load() method.

Now let’s take a look at what’s inside the SendNotifications() method.

I’ve declared a string type variable called message. Then I’m getting the connection string for my database. Using that connection string I’m opening a SQL connection to my database. In this example I’m using a simple SQL to get the data from Message column. Make sure your SQL statement meet the below requirements otherwise the notification won’t function properly (Either it will not get fired at all or it will fire multiple times for a one change).

http://msdn.microsoft.com/library/ms181122.aspx

Then I’m setting the Notification object of the SQL command to null. That’s because I need to clear the Notification object associated with the command. Next I create a new instance of the SqlDependency and associate it with my SQL command object. Then I attach the change event handler which is responsible for calling the same SendNotifications() method once a change occurred. You can see that inside the SendNotifications() method, I’m checking the type of the SqlNotification before I’m calling the SendNotifications(). I want to capture the data change only. That’s why I have that condition check.

Then I open the connection and execute the query. If the query returns any rows, I read the first result and pass that to hub method - NotifyAllClients(). As I said above, that hub method is responsible for distributing the message.

Now let’s take a look at the client side implementation of this.

First of all, I need to add the references to jQuery library, SignalR hub and the SignalR library.

<script src='<%=ResolveClientUrl("~/Scripts/jquery-1.6.4.min.js") %>' type="text/javascript"></script>
<script src='<%=ResolveClientUrl("~/Scripts/jquery.signalR-2.1.2.min.js") %>' type="text/javascript"></script>
<script src='<%=ResolveClientUrl("~/signalr/hubs") %>' type="text/javascript"></script>

Note that how I make use of ResolveClientUrl method to get the URL relative to the current page. I said my hub is calling a client method called displayNotification(). So I need to have an implementation for that. Also, I need to start the SignalR connection between client and server. Below code shows that

<script>
    $(function () {
        var notify = $.connection.notificationsHub;

        notify.client.displayNotification = function (msg) {
            $("#newData").html(msg);
        };

        $.connection.hub.start();
    });
</script>

If you are not familiar with the SignalR code, please have a look at my other blog post Creating a chat application in ASP.NET with SignalR. It describes What each code do.

In this example, I’m setting the message to a span with id=newData. Below is the HTML body for my page

<body>
    <form id="form1" runat="server">
        <div>
            <span id="newData"></span>
        </div>
    </form>
</body>

As you can see, it’s very simple. I wanted to keep it simple so you can understand the concept better.

Below is the full client side code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Messages.aspx.cs" Inherits="DatabaseNotification.Messages" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src='<%=ResolveClientUrl("~/Scripts/jquery-1.6.4.min.js") %>' type="text/javascript"></script>
    <script src='<%=ResolveClientUrl("~/Scripts/jquery.signalR-2.1.2.min.js") %>' type="text/javascript"></script>
    <script src='<%=ResolveClientUrl("~/signalr/hubs") %>' type="text/javascript"></script>
    <script>
        $(function () {
            var notify = $.connection.notificationsHub;

            notify.client.displayNotification = function (msg) {
                $("#newData").html(msg);
            };

            $.connection.hub.start();
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <span id="newData"></span>
        </div>
    </form>
</body>
</html>

Now let’s run it and see what’s happening. As you expected, there will be nothing displayed on the page. Let’s go to the Database and change the value in “Message” column. I changed the value “Hi SignalR” to “Hello SignalR”. Now you can instantly see the message on the browser.

image

This is a very basic example of how to trigger the database changes using SqlDependency and SignalR. You can download the source code from below.


Please note that SqlDependency will fire whenever there is a change but it won’t tell you which record got changed. It will simply return all the records according to the query. If you need to know what record got changed, you need to keep track of modified time in a separate column and query the results set according to that. I’m not going to talk about it in detail here.

Happy coding!