How to use SignalR in MVC & Communicate with the SQL Server Database

Introduction

In this tutorial, we will learn how to use SignaR in MVC application and also see how it works with a real-time data push in SQL Server. If you are new to SignalR then you must learn what is SignalR and where to use it.

What is SignalR?

SignalR is a library that adds real-time web functionality to applications. Real-time web functionality is the ability to have server-side code push content to the connected clients as it happens, in real-time. SignalR takes advantage of several transports, automatically selecting the best available transport given the client's and server's best available transport. SignalR takes advantage of WebSocket, an HTML5 API that enables bi-directional communication between the browser and server. SignalR will use WebSockets under the covers when it's available, and gracefully fall back to other techniques and technologies when it isn't, while the application code remains the same.

Why use SignalR ?

Using SignalR we can create web applications that require high frequency updates from the server. For examle, a dashboard, games and chat applications. SignalR uses Web Sockets and the HTML5 API that helps in bidirectional communication. It also provides an API for server-to-client Remote Procedure Calls (RPC) call, it may be something new for you because most of the time we use a request and response model.

SignalR includes automatic connection management, it provides the facility to broadcast messages to all connected clients or to a specific client. The connection between the client and server is persistent while in HTTP it isn't persistent.

Where to use SignalR ?

Chat: It is very easy to implement a chat application using SignalR, either it could be one-to one or a group chat.

Notification: If you want to notify 1 client or all clients then we can use SignalR. Notification can be like some alerts, a reminder, feedback or comments and so on.

Gaming: SignalR helps to create a Gaming application that requires frequently pushing from a server and so on.

Let's Create an MVC application in VS 2019 and connect with SQL server 2019.

Step-1

Create a SQL Server table on below attributes.

CREATE TABLE [dbo].[Employee](
[empId] [int] IDENTITY(1,1) NOT NULL,
[empName] [varchar](50) NOT NULL,
[Salary] [int] NOT NULL,
[DeptName] [varchar](50) NOT NULL,
[Designation] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[empId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Step-2

Add the SignalR package in Nuget:

Step-3

Add a Employee model inside the Models folder

public class Employee
{
public int empId { get; set; }
public string empName { get; set; }
public int Salary { get; set; }
public string DeptName { get; set; }
public string Designation { get; set; }
}

Step-4

Add the signalR Hub class inside Models folder named as "MyHub.cs"

namespace SignalR.Models
{
[HubName("myHub")]
public class MyHub : Hub
{
[HubMethodName("sendMessages")]
public static void SendMessages()
{
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<MyHub>();
context.Clients.All.updateMessages();
}
}
}

Step-5

We connect the database with SQL Dependency. First need to add the SQL Dependency in Global.asax

SqlDependency.Start(connString);

Now we create the Repository.cs inside Models folder and connect with database like below.

public class Repository
{
SqlConnection con = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
public List<Employee> GetAllMessages()
{
var messages = new List<Employee>();
using (var cmd = new SqlCommand(@"SELECT [empId],
[empName],[Salary],[DeptName],[Designation] FROM [dbo].[Employee]", con))
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
var dependency = new SqlDependency(cmd);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
DataSet ds = new DataSet();
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
messages.Add(item: new Employee
{
empId = int.Parse(ds.Tables[0].Rows[i][0].ToString()),
empName = ds.Tables[0].Rows[i][1].ToString(),
Salary = Convert.ToInt32(ds.Tables[0].Rows[i][2]),
DeptName = ds.Tables[0].Rows[i][3].ToString(),
Designation = ds.Tables[0].Rows[i][4].ToString(),
});
}
}
return messages;
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e) //this will be called when any changes occur in db table.
{
if (e.Type == SqlNotificationType.Change)
{
MyHub.SendMessages();
}
}
}

Step-6

Open the Index.cshtml file and call the SignalR hub. "myHub" is resposnible to call the SQL depedency when any change in SQL server.

@{
ViewBag.Title = "Home Page";
}
<!DOCTYPE html>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<link href="~/Content/cover.css" rel="stylesheet" />
@section Scripts{
@*<script src="~/Scripts/jquery-3.3.1.min.js"></script>*@
<script src="~/Scripts/jquery.signalR-2.4.1.min.js"></script>
<script type="text/javascript" src="/signalr/hubs"></script>
<script type="text/javascript">
$(function () {
// Declare a proxy to reference the hub.
var notifications = $.connection.myHub;
//debugger;
// Create a function that the hub can call to broadcast messages.
notifications.client.updateMessages = function () {
getAllMessages()
};
// Start the connection.
$.connection.hub.start().done(function () {
console.log("connection started")
//notifications.onconn();
getAllMessages();
}).fail(function (e) {
alert(e);
});
});
function getAllMessages() {
var tbl = $('#messagesTable');
$.ajax({
url: '/Home/GetMessages',
contentType: 'application/html ; charset:utf-8',
type: 'GET',
dataType: 'html',
success: function (result) {
console.log(result);
var a2 = JSON.parse(result);
tbl.empty();
var i = 1;
$.each(a2, function (key, value) {
tbl.append('<tr>' + '<td>' + i + '</td>' + '<td>' + value.empName + '</td>' + '<td>' + value.Salary + '</td>' + '<td>' + value.DeptName + '</td>' + '<td>' + value.Designation + '</td>' +'</tr>');
i = i + 1;
});
}
});
}
</script>
}
<div class="container">
<div class="panel-group">
<div class="panel panel-default">
<div class="panel-heading">Employee Information</div>
<div class="panel-body">
<div>
<table id="tab"></table>
</div>
<div class="row">
<div class="col-md-12">
<div>
<table class="table table-striped">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Salary</th>
<th>Department</th>
<th>Designation</th>
</tr>
</thead>
<tbody id="messagesTable"></tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>

The SignalR script is added to connect the Hub.
getAllMessages() is called to get the employee data.

Step-7

Now Add the OWINStatrupAttribute class in New Item - OWIN startup class

[assembly: OwinStartupAttribute(typeof(SignalR.Startup))]
namespace SignalR
{
public class Startup
{
public void Configuration(IAppBuilder app)
{
// For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=316888
app.MapSignalR();
}
}
}

Step-8

Now we insert the record into the table in some interval of times you can see the output.

DECLARE @i INT = 1;
DECLARE @sal INT = 78000;
DECLARE @sal_increment INT = 1;
WHILE (@i <=20)
BEGIN
WAITFOR DELAY '00:00:02'
set @sal_increment=5000
/*Your Script*/
insert into Employee
select 'Lori_'+cast(@i as varchar),@sal+@sal_increment,'IT','Software Engineer'
SET @i = @i + 1;
SET @sal = @sal + @sal_increment;
END

Now run the application and see the output. You can see in every 2 seconds we insert a record into table and it reflect on our page. Accordingly we can use it whenver it require scenario like Dashbaord notification, Website counter, Gaming, chat and many more...

Find Source code in Github

19 Comments

Join the conversation! Your thoughts help the community grow.

m taqi uzzaman1y ago
SqlDependency not working. Its only work if I refresh page
David Nguyen1y ago
Can we do this without SqlDependency? I am getting "The Sql Server Service Broker for the current database is not enabled" error and the company policy that It won't be anbled.
MuhammadShaheryarkhan2y ago
The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications. this error
Ivan SH3y ago
Thanks it's fair wich i was looking for <3
Atul Kadam4y ago
Want to use a stored procedure rather than a inline query. How do I proceed?
- SanSoft.InfoTech4y ago
  Read blog of sql stored procedure
William Oniszko5y ago
I want to do this, but i want to use a stored procedure rather than a table. Where do I begin?
- SanSoft.InfoTech4y ago
  Read blog of sql stored procedures
john mckinney5y ago
Just add that to Home Controller....
john mckinney5y ago
Public JsonResult GetMessages() { List<Order> orders = new List<Order>(); Repository r = new Repository(); orders = r.GetOrders(); return Json(orders, JsonRequestBehavior.AllowGet); }
john mckinney5y ago
This a bit confusing because in the jQuery function it refers to Home/GetMessages
Zeeshan Azim5y ago
Important Note: The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Otherwise, dependency_OnChange will not triggered.
Shahram Rad5y ago
Dear JAYANT I need To Someone expert in signalr to get help in my company's project .this is my email address : [email protected]
Manoj Phaju6y ago
Client updates only after page refresh.... what's the issue?
Yogesh Vedpathak6y ago
Nice Article Jayant ...
Sourav Kumar Das6y ago
Nice and useful article. Thanks for sharing.
Laxmidhar Sahoo6y ago
Thanks for a usefull article
Dipa Mehta6y ago
Nice article Jayant ...
- Jayant Tripathy6y ago
  Thank you..