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.
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:
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.
Great work! This is the type of information that should be shared around the internet. Shame on Google for not positioning this post higher! Come on over and visit my site . Thanks =)
help me out please
what is the meaning of
JavaScript runtime error: Unable to get property ‘client’ of undefined or null
Could you Share this sample code or send me pls, i may mistake..it is not working..
i need to control database by api ( incobilgisayar@gmail.com)
Thanks for share. my website using MVC. but removed cache, page load slow
if reload the page one change will call two times, and in the second scenario if we call client side code on other pages to get notification if we are in different page then on change also called for multiple times.
like if navigate to 5 pages Onchange will be call 5 times… this is the main problem..
i think it’s because the connection id repeating on each load how to restrict it, if this is the reason… help me please
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.
I want this answer if u get the answer please share with me . Thanks in advance
It Goes to Infinite Loop Help ME .!!! Message is showing again and again please help ..!!
In Debug mode my dependency_OnChange method firing continuously on Database change can you explain it is normal behaviour or it should not happen?
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.
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.
Nice article, easy to understand.
Hi I did the same thing using sqldependency problem was once i refreshed a page it will call many times
Hi I want this code in file if possible please mail me at aajitm@gmail.com
Are you using Entity Framework or ADO.NET in your project ?
can we implement this in ado .net?
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?
Brij, do you have a downloadable zip file for this project?
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.
i did not know how i execute 2 nd step….Please help..
Good job !
Perfectly executed in mvc4 web api application
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.
Does enabling service broker at database level has any performance impact? Can any one help me to know more details about this?
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?
–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
In the part: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO “DomainASPNET”
Try:
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO PUBLIC
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?
Thanks,
can i have the code of this sample!
Great!
” 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 !
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();
}
}
}
Hi have you get the solution? I am facing same issue.
Gud Work Dude..
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
sample code toh dedo dimag kharab ho gaya ho nahi raha
can anybody explain step 1 and 2. how to implement these step.
thanks in advance.
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..
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”)
it is a clear statement but Same error i got, sample should share..
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!!
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.
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??
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
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
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.
sql dependency does not work on system tables eg:dbo.sysjobactivity…how can i notify job status using sqldependency ?
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 ?
I have same issue.
if reload the page (F5), it works multiple events(dependency.OnChang).
How Can I solve this issue?
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?
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() ?
hi can i do the same using normal asp.net code not MVC? if yes can you please guide me
refer http://www.asp.net/signalr/overview/getting-started/tutorial-getting-started-with-signalr
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!!!
Make sure, javascript references are properly added.
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?
hey, it’s not working.. Could you help me.
Excellent Article. Thank you.
Chhay Taing: One of the reasons why there is continuous poll might be because of the sql command used in sql dependency:
1) Make sure you use [dbo].[table] name
2) Make sure you use fully qualified column names (Ex: Select [col_1],[col_2] From [dbo].[chat]).
here’s the full list: http://stackoverflow.com/questions/7588572/what-are-the-limitations-of-sqldependency/7588660#7588660
Hope this helps.
Very nice article. Could you share your solution´s files? It is for better understood
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?
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
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.
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.
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.
it’s not very chatty. it has a serious memory leak. it registers `dependency_OnChange` per each request and it will never unregister it.
This is happy-path-coding. What if your DB is down??? No ExceptionHandling in the repository, So a Job to do….
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 :-(
Brilliant ! Thanks for sharing. I’ looking forward to final SignalR 1.0