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.

notification Database Change Notifications in ASP.NET using SignalR and SqlDependency

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:

DB Structure Database Change Notifications in ASP.NET using SignalR and SqlDependency

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.

Comments:  32

  • Paul Van Bladel

    Brilliant ! Thanks for sharing. I’ looking forward to final SignalR 1.0

  • http://twitter.com/theDiverDK Søren Reinke

    Very nice article.

    But could you please make it so it is printable? The containers for the code, makes it not reader friendly on print :-(

  • Chhay Taing

    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.

  • Ingo Bleile

    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

  • Rajesh

    hey, it’s not working.. Could you help me.

  • Mo

    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!!!

  • http://www.techbrij.com Brij Mohan

    Make sure, javascript references are properly added.

  • Ankur Mistry

    hi can i do the same using normal asp.net code not MVC? if yes can you please guide me

  • Sharath Naik

    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() ?

  • Sharath Naik

    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?

  • Mo

    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?

  • qumquatsch

    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 ?

  • http://www.techbrij.com Brij Mohan
  • Arun

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

  • Ramvilas Varma

    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.

  • Aneesh Kamble

    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

  • Aneesh Kamble

    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

  • Giggles

    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??

  • geosdb

    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.

  • Curt Kauffmann

    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!!

  • Gaurav Gupta

    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..

  • somnath chaturvedi

    sample code toh dedo dimag kharab ho gaya ho nahi raha

  • Tridip Bhattacharjee

    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

  • Anto Reegan

    Gud Work Dude..

  • Hatem Hamad

    ” 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 !

  • Võ Tấn Tài

    Great!

  • Bilal Sami Sabha

    Thanks,

    can i have the code of this sample!

  • Tan

    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?

  • santosh kumar patro

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

  • Liran

    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();
    }
    }
    }

  • Kumar

    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.

  • bharadwaj gs

    Perfectly executed in mvc4 web api application