his article shows how to display real time sql server database change notification in ASP.NET usingSignalR 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:
1
2
3
|
<
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:
1
2
3
4
5
|
protected void Application_Start()
{
//...
SqlDependency.Start(ConfigurationManager.ConnectionStrings[
"DefaultConnection"
].ConnectionString);
}
|
To stop the listener:
1
2
3
4
|
protected void Application_End()
{
SqlDependency.Stop(ConfigurationManager.ConnectionStrings[
"DefaultConnection"
].ConnectionString);
}
|
5. Add following JobInfo and JobInfoRepository classes:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
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.RegisterRoutesmethod
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:
01
02
03
04
05
06
07
08
09
10
|
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:
01
02
03
04
05
06
07
08
09
10
11
12
|
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:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
@{
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