Jan 13, 2013

Database Change Notifications in ASP.NET using SignalR and SqlDependency

This article shows how to display real time sql server database change notification in ASP.NET using SignalR and SqlDependency object which represents a query notification dependency between an application and an instance of SQL Server. Consider your database is updated or synchronized by windows service in background, you have to display real time updated data. This article explains how to implement it.

database structure

DB Settings:

1. We need to enable the Service Broker on our database(TechBrijDB). To check, run following SQL syntax:

SELECT name, is_broker_enabled FROM sys.databases

To enable service broker on TechBrijDB database:

ALTER DATABASE TechBrijDB SET ENABLE_BROKER
GO

2. To subscribe query notification, we need to give permission to IIS service account

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO “Domain\ASPNET”

In our example, we are going to use JobInfo table:

database structure

Setup SqlDependency:

I am using ASP.NET Web API project but same steps can be applied on other ASP.NET projects also.

3. First define connection string in web.config:

<connectionStrings>
    <add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLExpress;Initial Catalog=TechBrijDB;Integrated Security=SSPI;" />
</connectionStrings>

4. In Global.asax, To enable listener:

 protected void Application_Start()
 {
		 //...
		 SqlDependency.Start(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString); 
 }

To stop the listener:

  protected void Application_End()
        {
            SqlDependency.Stop(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString); 
        }

5. Add following JobInfo and JobInfoRepository classes:

	public class JobInfo {
      public int JobID { get; set; }
      public  string Name { get; set; }
      public  DateTime LastExecutionDate { get; set; }
      public  string Status { get; set; }
    }
	
	 public class JobInfoRepository {

        public IEnumerable<JobInfo> GetData()
        {
          
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(@"SELECT [JobID],[Name],[LastExecutionDate],[Status]
                   FROM [dbo].[JobInfo]", connection))
                {
                    // Make sure the command object does not already have
                    // a notification object associated with it.
                    command.Notification = null;

                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    using (var reader = command.ExecuteReader())
                        return reader.Cast<IDataRecord>()
                            .Select(x => new JobInfo(){ 
                                JobID = x.GetInt32(0), 
                                Name = x.GetString(1), 
                                LastExecutionDate = x.GetDateTime(2),  
                                Status  = x.GetString(3) }).ToList();                            
                    


                }
            }        
        }
        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {            
            JobHub.Show(); 
        }

    
    }


In above code, On SqlDependency OnChange event, we are calling JobHub’s show method to refresh the grid. We will create Hub in next step.

SignalR:

6. To install Microsoft ASP.NET SignalR, run the following command in the Package Manager Console:

Install-Package Microsoft.AspNet.SignalR -Pre

Now in global.asax > Application_Start method, add following before RouteConfig.RegisterRoutes method

RouteTable.Routes.MapHubs();

You have to include following namespace:

using Microsoft.AspNet.SignalR;

7. Right click on project > Add new item > Select “SignalR Hub Class”, give name JobHub and add following method:

using Microsoft.AspNet.SignalR.Hubs;

 public class JobHub : Hub
    {
        public static void Show()
        {
            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<JobHub>();
            context.Clients.All.displayStatus();
        }
    }

8. In default valuecontroller, we are creating object of JobInfoRepository and on Get action, calling GetData method:

 public class ValuesController : ApiController
    {

        JobInfoRepository objRepo = new JobInfoRepository();


        // GET api/values
        public IEnumerable<JobInfo> Get()
        {
            return objRepo.GetData();
        }
	}

View:

For simplicity, we take same app to consume web api, Create an action say Status in Home controller, add a view without layout page:

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>JobStatus</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
    @Scripts.Render("~/bundles/jquery")
    <script src="~/Scripts/jquery.signalR-1.0.0-rc1.min.js"></script>
    <script src="~/signalr/hubs" type="text/javascript"></script>
    <script type="text/javascript">

        $(function () {

            // Proxy created on the fly
            var job = $.connection.jobHub;

            // Declare a function on the job hub so the server can invoke it
            job.client.displayStatus = function () {
                getData();
            };
         
            // Start the connection
            $.connection.hub.start();
            getData();
        });

        function getData() {
            var $tbl = $('#tblJobInfo');
            $.ajax({
                url: '../api/values',
                type: 'GET',
                datatype: 'json',
                success: function (data) {
                    if (data.length > 0) {
                        $tbl.empty();
                        $tbl.append(' <tr><th>ID</th><th>Name</th><th>Last Executed Date</th><th>Status</th></tr>');
                        var rows = [];
                        for (var i = 0; i < data.length; i++) {
                            rows.push(' <tr><td>' + data[i].JobID + '</td><td>' + data[i].Name + '</td><td>' + data[i].LastExecutionDate.toString().substr(0,10) + '</td><td>' + data[i].Status + '</td></tr>');
                        }
                        $tbl.append(rows.join(''));
                    }
                }
            });
        }
    </script>
</head>
<body>
    <div>
        <table id="tblJobInfo" style="text-align:center;margin-left:10px">
        </table>
    </div>
</body>
</html>

Open “http://yourserver/app/home/status” URL and test it.

On database change, dependency_OnChange method is triggered which calls Show method of signalR Hub. It notifies and triggers displayStatus method on client side to reset data.

In following video, I am changing values in table and it is reflected on the web page immediately:

If you are getting “TypeError: $.connection is undefined” javascript error then check your jquery.signalR path in script reference.

If you are getting “The connection to http://localhost … was interrupted while the page was loading” javascript error then check your jquery ajax url in GetData method.

Hope, you enjoy it.

61 comments

  1. Very good implementation, One issue,

    Only thing is on Notification all dB table records are fetched calling get method, how to get only the new row Inserted in to dB table.

  2. In Debug mode my dependency_OnChange method firing continuously on Database change can you explain it is normal behaviour or it should not happen?

  3. help me understand this ,i also have this problem. using web api 2 project for services and mvc 5 project as client app,my hub is at web api now i want to call a hub method from client application. #1. i am not able to generate automatic proxy probably because of separate projects. #2. if i use without generated proxy method ,server method dosent gets called , i get error at console log that ,error calling server method.

  4. First check whether “job” variable is undefined or not. I think it is undefined in your case. One of the solution for your issue is to add ” ” to the head tag after signal ir reference.

  5. Up to how many rows, in the sql server database, will these updates truly appear to be real-time? I added 10,000 rows to my database and whenever I add a new record, it takes up to 3 – 4 seconds to appear, on the table. Also, when I press F12, in Mozilla to open firebug, I see GET poll?transport=longPolling&connectionToken..
    I thought SignalR would leverage web sockets?

  6. Hi,
    This is a nice tutorial. But can you please provide the code for other asp.net web site(except mvc). I’m totally new in signalr. And I don’t know mvc. I have created a asp.net website and want to add that feature intomy web site. So please help me to do this.

  7. Hi,
    am new to signal R. the hub is started successfully.i checked using debug mode, when the cursor point comes to line no 35, its jumbing out of that ajax function. Pls any one can give me a solution for this.
    Thanks in advance.

  8. Does enabling service broker at database level has any performance impact? Can any one help me to know more details about this?

  9. I have problems with the statement GRANT SUBSCRIBE QUERY NOTIFICATIONS TO “DomainASPNET”, the sql server tell me “Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.” How can I fix it?

  10. –Habilitar el service broker
    ALTER DATABASE TechBrijDB SET ENABLE_BROKER

    –Verificar que nuestra bd tenga habilitado el broker
    SELECT name, is_broker_enabled FROM sys.databases

    –Dar permisos al sql de enviar notificaciones al usuario
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO public

  11. In the part: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO “DomainASPNET”

    Try:
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO PUBLIC

  12. First of all, thank you for the sample codes!

    I am working on a project base on the sample codes you provided. I observed when more than one instance of page was opened, the number of AJAX calls to get the data increases with each update to the table.

    Refer to screen shot. After several updates to the table, with two instances of the same page opened, a single update to table now made 256 AJAX calls to get data.
    http://1drv.ms/1wFsyOH

    Did you observe similar behavior in your solution?

  13. ” Error 1 ‘System.Web.Routing.SignalRRouteExtensions.MapHubs(System.Web.Routing.RouteCollection)’ is obsolete: ‘Use IAppBuilder.MapSignalR in an Owin Startup class. See http://go.microsoft.com/fwlink/?LinkId=320578 for more details.’ c:usershatemdocumentsvisual studio 2013ProjectstestNotiftestNotifGlobal.asax.cs 22 13 testNotif

    plz help !

    1. Install OWIN and OWIN Security using NuGet

      and add the following class to your solution App_Start folder

      using Microsoft.Owin;
      using Owin;

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

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

  14. I go through your article and it looks file but i have few question on this.
    1) Polling for database changes with SqlDependency is good option when huge number of client is changing data in table ?

    please see the line in this article
    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
    JobHub.Show();
    }

    when data will change then dependency_OnChange event will fire and JobHub.Show(); is calling

    JobHub is name of class and not static class so i like to know how anyone can call `JobHub.Show();` from out side ??

    3) what is GlobalHost class and when it is used ?

    4) the article code related issue.
    see the jquery code in view which fetch data and populate table. first time when page load suppose there is 5 records exist in table so 5 records will be passed to jquery code and it will just display those 5 records but when any existing data will be change in table then what will happen ??

    the only changed rows will come to client side or all data including changed data will come to client side ?

    if you say only changed data will come . it is shown in the video data is changed one by one and change is reflecting at client end but if you see the jquery code it just empty the table first and build the table again. so my question is if data change and only change data will come then one row should display at client side…. am i right. but in video the change is showing as well as other data is too in table.

    looking for answer. thanks

  15. It’s Great to work, But I have an issue. Every Time when I changing in the database, every time the request to get the data from the database is increasing ….

    I check this through Make a Breakpoint on GetData() Method.. It is firing more than the browser open..
    How to solve this..

  16. In my application, page is loading, I am not get any output and exception. how to find the issue. The finally blank page is showing. FYI, In my page, i removed the below code, @Styles.Render(“~/Content/css”)
    @Scripts.Render(“~/bundles/modernizr”)
    @Scripts.Render(“~/bundles/jquery”)

  17. I am getting the following error:

    Unhandled exception at line 24, column 13 in http://localhost:52133/home/status

    0x800a138f – JavaScript runtime error: Unable to get property ‘client’ of undefined or null reference.

    job.client.displayStatus = function () {
    getData();
    };

    my javascript references are:

    You explain to make sure the javascript references are correct. I have done that and still get the error. Is there something else that can cause this error. Please help!
    Thanks so much!!

  18. The reason some processes only fire once is because the SQLDependency call may not have time to complete. It’s a round trip from the process to the SQL service and back. So insert a Sleep(200) right after the call “SqlDependency dependency = new SqlDependency(command);” and you’ll probably give the system enough time to register the dependency.

  19. excuse me!!i am a newbie in Sql Dependency and i got “Cannot find the user ‘domain/ASPNET’, because it does not exist or you do not have permission.”
    what is domain and ASPNET, how i can retrieve them??

  20. The notification I get on all the clients should be simultaneous as per your video. But, in my application ,it is received by clients one by one after some delay. I m not able to figure out, whats the problem

  21. Amazing Article.. It works fine..But, I have some issue in SqlDependency.Start. It runs correctly, but after some time, it hangs on SqlDependency.Start statement. It just showd the loading of page in progress. ANy Ideas what must have happened

  22. Hi,
    This is best example you have provided, But I tried to do same thing with ASP.NET not MVC project, It not work for me. OnChanged Event of SQLDependancy is not firing.

  23. sql dependency does not work on system tables eg:dbo.sysjobactivity…how can i notify job status using sqldependency ?

  24. Somehow this works only once for me. The first change in the db is pushed, after that nothing happens. After reloading the page (F5) it works again for one time. Any idea what could be the reason for that ?

    1. I have same issue.
      if reload the page (F5), it works multiple events(dependency.OnChang).
      How Can I solve this issue?

  25. Hello,

    I have a Question,

    How is url: ‘../api/values’ (used in Ajax call of getData function) being resolved?
    If i have to change the url to some other controller where should I Specify it?

  26. Awesome Article.

    I am Impressed with the SignalR – SQL Dependency working together to do a Server push on all the clients.

    How ever I have noticed that OnChangeEvent handler will not get registered untill the command.ExecuteReader() is executed. If i don’t want to Execute the Select statement (Query), but only wanted to get notified about the changes, how can I avoid using command.ExecuteReader() ?

  27. I got this error! when I tried your code the only this is different is signalR edution I have signalR ( jquery.signalR-1.1.2.js)

    Unhandled exception at line 24, column 13 in http://localhost:52133/try.aspx

    0x800a138f – JavaScript runtime error: Unable to get property ‘client’ of undefined or null reference.

    job.client.displayStatus = function () {
    getData();
    };
    so what is the problem plz help!!!

      1. I get it to work! thanks a lot! but is it possible to include update SQL statement? if I add where condition to my select will that effect the efficiency of the system?

  28. Thanks for this post!

    However, I’m having an issue with your example.

    Specifically with the

    job.client.displayStatus = …

    I get an error

    Error: Unable to get value of the property ‘client’: object is null or undefined
    Any ideas as to where to troubleshoot?

  29. Matt – have you got a working example for your way of doing it? I’m new to SqlDependencies & SignalR and any help would be apreciated. Thank you! iB

  30. your solution seems to not be working with the sqldependency but instead just polls constantly. i’ve stepped into it with a debug statement and see an infinite loop occuring with the line: JobHub.Show() which in turn starts the cycle again. if you were to look at the sqldepedency arg and look at its info property, it’ll have a info type of invalid. if you were to check on that and then if it’s not invalid fire JobHub.Show() then it’ll work correctly… however, im having issues setting up my sqldependency object to fire correctly.

    1. There isn’t an answer for this yet, i dont understand the infinite looping aside the fact that when the method where the sqldependency resides has been invoked, it invokes the dependency action again, how to resolve this problem already.

  31. So, if your hub has 100 clients, whenever the SqlDependency Change notification fires, the change handler tells your SignalR hub to tell all 100 of its clients to each call back to the server asking for all of the data.

    I understand this is just a proof of concept, but this seems pretty chatty.

    Wouldn’t a better design to be to have the change handler get all the data once, then pass it to the SignalR hub, which would, in turn, broadcast the data out to all the clients, rather than having them each go back and as the server for the data?

    Also, when I tried to do this, I would always run into trouble reacting to IIS process recycles. If I’m parsing your code correctly, if your thread pool dies/resets/recycles, there is nothing that re-registers the SqlDependency (nothing calls JobRepository.GetData()), and you would have nothing with a change handler listening for changes.

    1. it’s not very chatty. it has a serious memory leak. it registers `dependency_OnChange` per each request and it will never unregister it.

      1. This is happy-path-coding. What if your DB is down??? No ExceptionHandling in the repository, So a Job to do….

Leave a Reply

Your email address will not be published. Required fields are marked *