 |
| We will see output like this, when any data insert into database it reflect on page |
Let's Create an MVC application in VS 2019 and connect with SQL server 2019. If you are new to VS 2019, please check how to create MVC application in visual studio 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]
Add the SignalR package in Nuget:
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; }
}
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();
}
}
}
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();
}
}
}
Open the Index.cshtml file and call the SignalR hub
@{
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-7Now 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();
}
}
}
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
</> The Source Code is available in Github.com/CoreProgramm/Summary
In this tutorial we discussed how to use SignalR in MVC. If have any question related to this topic then give your feedback.
Post a Comment