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!

59 comments:

Anonymous said...

Thanks for the example, i've been looking for something like this. However, my requirement is for MVC based project.
1. Could you provide an example where the 'Page_Load' equivalent event can be handled in MVC.
2. If i understand it correctly, the dependency onchange event would be fired whenever a new row is inserted or the message column is updated?

Ruchira Gamage said...

Hi,
1. I'm not much familiar with MVC. But I think in MVC, you can do the same in an Action method. The same code I call inside the Page_Load method can go in to the relevant controller's Action method which return the particular view.

2.That's correct. It will fire whenever a new row is inserted or a change occurred in an existing row.

Anonymous said...

one problem is there.u developed the notification for specific page called "Messages". there is one problem that if Mr x send some data to Mr y and if Mr y is not in Messages page then he will not get any notifivation.

try to do it in global way like wherever Mr Y is but he will get notification. if i am not clear then let me know so i will again explain the whole things. my mail id is tridip@bba-reman.com. thanks

Ruchira Gamage said...

Sure they don't. SignalR works only if you have both clients up and running. If there is one client disconnected, you can't send messages to that client. There need to be a connection in order to communicate.

You can develop other clients like Win 8 apps, iOS or Android apps so if the Mr Y is on any of those client, he will get the message.

Or you can save the data in a database or Azure table so they will see the message whenever they come online. Check the below link for more details

http://www.asp.net/signalr/overview/guide-to-the-api/mapping-users-to-connections

simon zupan said...

Hi,

there is some mistake here.
Every time when change in the database happened, the displayNotification method is executed 4 times.
If you change the code:
var i = 0;
notify.client.displayNotification = function (msg) {
i = i + 1;
$("#newData").html(msg+i.toString());
};
you will see the result.

What is happening?

Ruchira Gamage said...

Hi Simon,

I just tried it and I'm not getting your results (i.e. I'm getting sequentially incremented value of i variable.) Probably it has something to do with how you are calling it. Check your server side code and see whether there are any loop or something like that.

Unknown said...

Hi

Thanks for the provided code, it works excellent, however when the page loads it doesn't display anything at all since it only display changes or new records in the database. What would you sugest me to do if I need to have all the records from the database displayed on page load?

/Magnus

Ruchira Gamage said...

Hi Magnus,

The purpose of my example code is to show how SignalR works with SqlDependency so I kept it simple as possible. If you need to load all the records from database displayed on page load use some simple data-bound control like gridview or you can manually get the data from database using ADO.NET/Entity Framework and display the data.

prototype 2 rip said...

Thanks for the provided code, it works excellent, however when the page loads it doesn't display anything at all since it only display changes or new records in the database.

Ruchira Gamage said...

Hi,

Yes. That's the idea. Please refer to my above reply.

INP_Koes said...

I tries this code , getting values from DB but not showed on Span tag?
can you please help me to solve
if you will give the email id i will send the code.

Ruchira Gamage said...

Hi,

Check the browser console for any errors.

INP_Koes said...

Dear Ruchira Gamage,
thanks for your reply,
It is working locally if i used div before the Form runat=server tag,but same thing i uploaded on server it is not working , i tried lot i am getting 404 error
http://stackoverflow.com/questions/11155064/signalr-signalr-hubs-404-not-found
I tried through this links solutions i am getting same error.
my website link is
http://chat.gofabby.ae/
Please help me to solve,thanks in advance

Ruchira Gamage said...

404 means the file not found. Please check the paths of your referenced files.

INP_Koes said...

Thanks for your immediate reply,now it is working,problem is in server,it was framework 3.5,now we asked to change the server admin to change 4.5. one more request can you please advice to use context.Clients.All.displayNotification with posted datetime parameter like gofabby.com Chat
name Date withtime break
message content

INP_Koes said...

Dear Ruchira,
I want to use same like http://chat.gofabby.ae/messages.aspx
in http://chat.gofabby.ae/default.aspx
Please help me

Syed Ibrahim said...

Dear Ruchira,
I am new to this SignalR,
Can you please suggest me how to use
the following functions with more than 2 variables ,like userName,message,posteddate
AddMessageinCache(userName, message);
Clients.All.messageReceived(userName, message);

Ruchira Gamage said...

Please follow my other blog post here http://ruchirac.blogspot.co.nz/2013/02/creating-chat-application-in-aspnet.html

Anonymous said...

hello sir,

When i added this line-

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

I got following error-
The type of namespace name OwinStartup could not be found

Ruchira Gamage said...

Hi,
You need to install Owin from Nuget package manager.

Fhahroz said...

Hello;

You example is working perfectly fine while retrieving data from the database. But when i change you query to
"Select count(Messages) as [Total] from MyTable"
is doesn't reflect any result on the page.

Is there anything to be added to perform this task?

Please help.
Regards

Ruchira Gamage said...

Hi Fhahroz,

Please have a look under "Supported SELECT statements" on below link and make sure your query meet those requirements

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

Fhahroz said...

Hello,

Thanks for the reply, I followed that link you provided and i am pretty much cleared now.

One more thing, i am trying to bind the number of counts on page load. It seems like signalr is kind of overwriting my calls.

I have called my method bindVal() on page load and then your method SendAllNotification() is called.

When I debug the code the count is shown but then suddenly it is gone and is set back to default that is "0" and starts to change only when i submit data to my db (That's exactly how it should work) but no data on page load once shown but then it is gone suddenly.

I tried to remove your function from page load event, i even tried to put my function in if(!Page.IsPostBack) but i am still failing can you please help me out in this one too.

P.S. I really appreciate your work.

Regards

Unknown said...

Ruchira,

Like Simon Zupan I’ve also noticed displayNotification firing multiple times. If you create a static int within the NotificationsHub that increments when executed and use this instead of msg you will see it increment as expected until the page is refreshed or additional clients connect. Once either a page refresh or new client connects the counter will fire multiple times, this is proportional so for example two page refreshes will cause the displayNotification to fire three times per db change. I believe the problem is that we should be using a Singleton Instance like this example - http://www.asp.net/signalr/overview/guide-to-the-api/hubs-api-guide-server#callfromoutsidehub
What do you think?

Ruchira Gamage said...

Hi,

If the field is marked as static, then yes, you should use Singleton pattern to ensure the thread safety. Apart from the Signalr stock ticker example on github and official documentation, you can also look at this post - https://franroca.wordpress.com/2012/04/13/binding-signalr-hubs-to-server-events/

INP_Koes said...

Dear Ruchira,
I need a source code for signalR, 1.Private chat with Offline message

2. Private chat window should be draggable. now i am getting one private Window only visible and also i can't move from the current document place.i.e fixed the private window

INP_Koes said...

I am new to signalR,

Can you please provide me the Private chat with Offline messages.
Private chat window should be draggable or movable from any where in the page or move from the frame.

Vasco said...

Hi Ruchira,
I’m beginner in vb.net and I spent some time trying to understand what was it the SignalR I found a lot of examples but the people tend to complicate what is simple, with your example I easily understand the concept (that's the point), I think from your base anyone can progress to improvements. Thank you for sharing… grateful just great

Ruchira Gamage said...

Hi Vasco,

Thanks a lot for your nice comment!

INP_Koes said...

hi,
Please guide me to do private chat with offline messages.

Anonymous said...

Hi Ruchira,
First of all, let me thank you so for this example.
But I got an error and I couldn't get the reason, I have modified this line " $("#newData").html(msg)" to be "$("#newData").html(msg + '
' + $("#newData").html());", this is because I want to get all online changes and it works fine, but if I refreshed the page I got the results twice and if I refreshed it again I got the results triple and so on .., so what do you suggest to do to avoid that.
Thank you in advance

Anonymous said...

Did anyone find a solution for the problem of repeating results after refresh?

/Ahmed

Ruchira Gamage said...

Hi Ahmed,

Then you need to implement a Singleton class as shown in below example

http://www.asp.net/signalr/overview/getting-started/tutorial-server-broadcast-with-signalr

vamshi rao said...

How to implement stored procedure instead of query to get notifications?

TumeloM said...

Please help me to solve this error

Server Error in '/' Application.

When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.

Source Error:


Line 40: connection.Open();
Line 41:
Line 42: SqlDataReader reader = command.ExecuteReader();
Line 43:
Line 44: if (reader.HasRows)


I have Global.asax :
public class Global : System.Web.HttpApplication
{

protected void Application_Start(object sender, EventArgs e)
{
SqlDependency.Start(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString);
}


protected void Application_End(object sender, EventArgs e)
{
SqlDependency.Stop(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString);
}
}

Ruchira Gamage said...

And do you have the connection string named "TestDB" in your web.config as well? If so, make sure your web application has permission to access that database

Arjun Singh said...

I wanted to ask its for single data .. how can i do it for multiple rows since this update one data

Ali said...

Please correct the spelling of hub method name. Its written NotfiyAllClients by mistake.

I wonder how this worked if you copy-pasted the code from your dev editor? :)

Ruchira Gamage said...

Thanks Ali. I've corrected it now. If you look at the source code you will see that it's correct (Spelling mistake is only in the blog post). I might've manually typed that part because it's a pain to insert code in blogger. Anyway, thanks a lot for spotting that!

Thilanka Ishara said...

Hi, Richira Aiya, your code is excellent and it was very helpful to me, it returning only one row. i want to get only newly inserted row , how can i do that, can u help me with this please.

thanks

Ruchira Gamage said...

Hi Thilanka Malli,

I'm not sure what you meant by newly inserted row. The code I've provided return the set of records which satisfy the query. In this case all the records in DummyData table.

If you want only the newly created data, then you can create a new column in the database which holds the created datetime and change the query with ORDER BY created date. Then you can get the first record in that result set.

嚴靖雅 said...

Hi, Richira Aiya, your code is excellent and thank you for sharing those code.
But I have one question.
Why the PageLoad() needs to call this function:SendNotifications()?
When I move this code, this project can't work correctly.

Is this function following not a active function?
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change)
{
SendNotifications();
}
}

Ruchira Gamage said...

Hi,

For the initial load, you need to create the SQL dependency and register the change event handler. That's why we have to call SendNotifications() method in Page_Load().

dependency_OnChange is the method I bound to the change event. It will get triggered by the Sql Dependency. We are not calling it explicitly.

Ali said...

@Thilanka Ishara

This link might help you. Try it please.
http://stackoverflow.com/questions/32039112/sqldependency-onchange-firing-but-sqldatareader-is-not-returning-with-data

嚴靖雅 said...

Thank you for replay first.
In that case, the Sql Dependency must be initial when the page is loaded.
But, I need to select the data after users key in their information, such as their accounts.
If the select sting is empty when the page is loaded, I will get error.
Do you have any suggestion to solve this problem?

Kashif said...

I am using the same code , I go to database and change values in fields but that does not appear on the page , it must get automatically.
I think i am missing some thing.
Please help me.

Ruchira Gamage said...

check the browser console for any errors, debug your code and see. Also 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

Imran Haider said...

Hi, thank you for this excellent code. It's working perfectly with my newly created DB but not existing DB. Have you any Idea?

Guin Box said...

thanks , but notify.client.displayNotification not work and private void dependency_OnChange(object sender, SqlNotificationEventArgs e) not run
whate should i do , please ?

Ruchira Gamage said...

Make sure your SQL query match the below
http://msdn.microsoft.com/library/ms181122.aspx

嚴靖雅 said...

If I use a new database, those code are run perfectly.
But, if I connect to another existed database, those code can't work.
The ID of hub can be got by javascript but the message can't update.
The SQL query is checked and everything is correct.
Now I have no idea what I lost, can you give me some hint?

Anonymous said...

Hi, I find the answer about moving the database this morning.

http://yourprosoft.blogspot.tw/2010/12/old-dbo-database-owner-prevents-service.html

INP_Koes said...

hai Can you please help me to solve the following problem.
I am facing another problem that connection opened from one main/sub domain is not sending messages to the connection opened with another sub domai while using signalR with multiple subdomains.


Does signalR hub differentiation is domain based or IP address based?

Tekin said...

Hi ,

How can i use signalR database change notification for asp.net webforms

abel gebray said...

hi sir am kinda stuck on getting it on gridview displaying the old data to i want it to display all the old and the new changes together help please?????

Chandan Dey said...

Hi,
First of all thanks for this valuable example.
But I noticed one think, suppose I have existing data in my database and I start my application there is no data showing in my page after change in data in database table field the data will display.
Can you tell me why?
thanks,
Chandan

dev chaudhary said...

hii,
I am getting same problem like Chandan Dey
Please Sir Help Me...

dev chaudhary said...

hii
Sir, I am getting Same Problem Like Chandan Dey
Please Help Me Sir....

Sanjay Shenvekar said...

Hi Ruchira,

I have an issue on line "var notify = $.connection.notificationsHub;"

Mu hub class name is NotificationsHub.

I have put the first letter in small in java script but it gave me an error
"cannot read property notificationsHub of undefined.

Please can you help me here

Regards
Sanjay