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