In this article, we will learn about instant notification on database changes using SignalR with MVC.
What is SignalR?
ASP.NET SignalR is a library for .NET developers that simplifies the process of adding real-time web functionality to an application. Real-time web functionality is the ability to have server code push content to connected clients instantly as it becomes available, rather than having the server wait for a client to request new data.
Prerequisites
Let us start with an example,
Step 1: Create a Database and Table
Let us first create a database namely [dbTempSignalR] and create a table [dbo].[Flower] by executing the below script,
USE [dbTempSignalR] GO /****** Object: Table [dbo].[Flower] Script Date: 6/24/2022 02:56:14 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Flower]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [Price] [int] NOT NULL, CONSTRAINT [PK_Flower] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO
Step 2: Enable Service Broker on the database
Service Broker is the feature introduced for the first time in SQL Server 2005. By using this feature, external or internal processes can send and receive asynchronous messages reliably by using extensions of SQL Data Manipulation Language (DML). It is a queued and reliable messaging mechanism used for asynchronous programming models.
We need this feature to be enabled since, whenever a change in the table will happen like (INSERT/UPDATE/DELETE/TRUNCATE), then the SQLDependency will identify that. It (Service Broker) implements a Broker Architecture that publishes the events while the SQLDependency acts as a subscriber and detects the changes. Using the SQLDependency object, the application can create and register to receive notifications via the OnChangeEventHandler event handler.
Now, we will use the below statement to check whether the service broker is enabled or not for the above-created database.
SELECT NAME, IS_BROKER_ENABLED FROM SYS.DATABASES WHERE NAME='dbTempSignalR'
Result,
Now, we will enable the service broker for database [dbo].[dbTempSignalR] by using the below statement,
ALTER DATABASE dbTempSignalR SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
After executing the above statement let’s check again whether the service broker enables or not,
Step 3: Let us create the MVC project by selecting an ASP.NET Web Application (.NET Framework) and then clicking on the Next button.
Now give the project name “SignalRDemo” and click on Create button.
And then, select MVC and click on Create button.
Step 4: Open Web.config file and add Connection string between <configuration>…</configuration> tag as below,
<connectionStrings>
<add name="ConSignalR" providerName="System.Data.SqlClient" connectionString="Data Source=DESKTOP-5Q82EP7;Initial Catalog=dbTempSignalR;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" />
</connectionStrings>
Step 5: Open Global.asax.cs file and modify as below,
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;
namespace SignalRDemo
{
public class MvcApplication : System.Web.HttpApplication
{
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
//SqlDependecy for monitor whether any modification done in database
SqlDependency.Start(ConfigurationManager.ConnectionStrings["ConSignalR"].ConnectionString);
}
protected void Application_End()
{
SqlDependency.Stop(ConfigurationManager.ConnectionStrings["ConSignalR"].ConnectionString);
}
}
}
Step 6: Go to Tools->Nuget Package Manager->Package Manager Console as below,
Then, write the below statement to install the SignalR package into your project.
Install-Package Microsoft.AspNet.SignalR -pre
Step 7: Now add the OWIN Startup class with the name Startup.cs as below,
Right-click on the project name and Goto Add->Class as below,
Then, search Startup class and select OWIN Startup class and give the name “Startup.cs” and click Add button as below,
Now modify the Startup.cs file as below,
using Microsoft.Owin;
using Owin;
using System;
using System.Threading.Tasks;
[assembly: OwinStartup(typeof(SignalRDemo.Startup))]
namespace SignalRDemo
{
public class Startup
{
public void Configuration(IAppBuilder app)
{
app.MapSignalR();
}
}
}
Step 8: Create a new folder by Right Click on Project name-> Add->NewFolder and giving the name Hubs.
Now, Right-click on Hubs folder->Add->Class and search “Hub” and select “SignalR Hub Class (v2)” give class name “NotificationHub.cs” as below,
Then modify NotificationHub.cs file as below,
using Microsoft.AspNet.SignalR;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace SignalRDemo.Hubs
{
public class NotificationHub : Hub
{
public static void Show()
{
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
context.Clients.All.displayCustomer();
}
}
}
Step 9: Now, Create a controller namely “NotificationsController.cs” and Modify it as below,
using DemoDatabaseChangeNotification.Hubs;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace SignalRDemo.Controllers
{
public class NotificationsController : Controller
{
// GET: Notifications
public ActionResult Index()
{
return View();
}
public JsonResult Get()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conSignalr"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(@"SELECT ID,Name,Price FROM [dbo].[Flower]", 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();
//command.ExecuteNonQuery();
SqlDataReader reader = command.ExecuteReader();
var listEmp = reader.Cast<IDataRecord>()
.Select(x => new
{
ID = (int)x["ID"],
Name = (string)x["Name"],
Price = (int)x["Price"],
}).ToList();
return Json(new { listEmp = listEmp }, JsonRequestBehavior.AllowGet);
//return Json("" , JsonRequestBehavior.AllowGet);
}
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
NotificationHub.Show();
}
}
}
Here, In the above controller, we are using SqlDependency that will determine whether any changes are done in the database or not. On any changes, the OnChangeEventHandler event handler is executed and the method of NotificationHub class will be called.
Step 10: Now, Create the view file “Index.cshtml” and Modify it as below,
@{ ViewBag.Title = "Notifications"; }
<h2>Notify on Database Changes</h2>
@Html.Hidden("Get", Url.Action("Get", "Notifications"))
<table class="table">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Price</th>
</tr>
</thead>
<tbody id="tblInfo">
</tbody>
</table>
@section scripts {
<script src="~/Scripts/jquery-3.4.1.min.js" ></script>
<script src="~/Scripts/fake-push.min.js"></script> @*Push notification plugin refernce*@
<script src="~/Scripts/jquery.signalR-2.4.3.min.js"></script>
<script src="~/signalr/hubs" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
// Proxy created on the fly
//hubClass name should be in camelCase (eg. notificationHub)
var cus = $.connection.notificationHub;
// Declare a function on the job hub so the server can invoke it
cus.client.displayCustomer = function () {
getData();
$.push(/*Title*/'Notification',/*Message*/'Database Updated',/*icon*/'');
};
// Start the connection
$.connection.hub.start();
getData();
});
function getData() {
var $tbl = $('#tblInfo');
$.ajax({
url: $("#Get").val(),
type: 'GET',
datatype: 'json',
success: function (data) {
$tbl.empty();
$.each(data.listEmp, function (i, model) {
$tbl.append(
'<tr>'
+ '<td>' + model.ID + '</td>'
+ '<td>' + model.Name + '</td>'
+ '<td>' + model.Price + '</td>'
+ '<tr>'
);
});
}
});
}
</script>
}
Here, In the above Index.cshtml file we are using a Plugin for Push Notification, From this Download Plugin Link, you can download the plugin. After downloading the plugin, we can see the “fake-push.min.js” file.
Simply Copy that file and paste it into your project in the “~/Scripts/” folder. Then, give reference of <script src=”~/Scripts/fake-push.min.js”></script> into Index.cshtml file. Then write ‘$.push(“Title”, “Message”, “Icon”);‘ statement into displayCustomer function for Push notification.
[Note:
- In above Index.cshtml we can see the statement “var cus = $.connection.notificationHub;” In this statement notificationHub is a Class name that we create in Hubs Folder. Write this class name Must be in camelCase in Index.cshtml file.
- Check the version of jquery-3.4.1.min.js and jquery.signalR-2.4.3.min.js file with the file available in the “~/Scripts/” folder whether it is matching or not.]
Demo:
Download Links:
I hope this blog will help you to understand the concept of instant Database changes notification using SignalR with MVC.
Thank You.
