Post
Ask Question

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.
  1. CREATE TABLE [dbo].[Employee](  
  2. [empId] [int] IDENTITY(1,1) NOT NULL,  
  3. [empName] [varchar](50) NOT NULL,  
  4. [Salary] [int] NOT NULL,  
  5. [DeptName] [varchar](50) NOT NULL,  
  6. [Designation] [varchar](50) NOT NULL,  
  7. PRIMARY KEY CLUSTERED  
  8. (  
  9. [empId] ASC  
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
  11. IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  
  12. ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  13. ) ON [PRIMARY]  
 Step-2
 Add the SignalR package in Nuget:
 signalR
Step-3
 
Add a Employee model inside the Models folder
  1. public class Employee  
  2. {  
  3. public int empId { get; set; }  
  4. public string empName { get; set; }  
  5. public int Salary { get; set; }  
  6. public string DeptName { get; set; }  
  7. public string Designation { get; set; }  
  8. }  
 Step-4
 
Add the signalR Hub class inside Models folder named as "MyHub.cs" 
  1. namespace SignalR.Models  
  2. {  
  3. [HubName("myHub")]  
  4. public class MyHub : Hub  
  5. {  
  6. [HubMethodName("sendMessages")]  
  7. public static void SendMessages()  
  8. {  
  9. IHubContext context = GlobalHost.ConnectionManager.GetHubContext<MyHub>();  
  10. context.Clients.All.updateMessages();  
  11. }  
  12. }  
  13. }  
Step-5
 
We connect the database with SQL Dependency. First need to add the SQL Dependency in Global.asax
  1. SqlDependency.Start(connString);  
Now we create the Repository.cs inside Models folder and connect with database like below. 
  1. public class Repository  
  2. {  
  3. SqlConnection con = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);  
  4. public List<Employee> GetAllMessages()  
  5. {  
  6. var messages = new List<Employee>();  
  7. using (var cmd = new SqlCommand(@"SELECT [empId],  
  8. [empName],[Salary],[DeptName],[Designation] FROM [dbo].[Employee]", con))  
  9. {  
  10. SqlDataAdapter da = new SqlDataAdapter(cmd);  
  11. var dependency = new SqlDependency(cmd);  
  12. dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);  
  13. DataSet ds = new DataSet();  
  14. da.Fill(ds);  
  15. for (int i = 0; i < ds.Tables[0].Rows.Count; i++)  
  16. {  
  17. messages.Add(item: new Employee  
  18. {  
  19. empId = int.Parse(ds.Tables[0].Rows[i][0].ToString()),  
  20. empName = ds.Tables[0].Rows[i][1].ToString(),  
  21. Salary = Convert.ToInt32(ds.Tables[0].Rows[i][2]),  
  22. DeptName = ds.Tables[0].Rows[i][3].ToString(),  
  23. Designation = ds.Tables[0].Rows[i][4].ToString(),  
  24. });  
  25. }  
  26. }  
  27. return messages;  
  28. }  
  29. private void dependency_OnChange(object sender, SqlNotificationEventArgs e) //this will be called when any changes occur in db table.  
  30. {  
  31. if (e.Type == SqlNotificationType.Change)  
  32. {  
  33. MyHub.SendMessages();  
  34. }  
  35. }  
  36. }  
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.
  1.    
  2. @{  
  3. ViewBag.Title = "Home Page";  
  4. }  
  5. <!DOCTYPE html>  
  6. <meta name="viewport" content="width=device-width" />  
  7. <title>Index</title>  
  8. <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">  
  9. <link href="~/Content/cover.css" rel="stylesheet" />  
  10. @section Scripts{  
  11. @*<script src="~/Scripts/jquery-3.3.1.min.js"></script>*@  
  12. <script src="~/Scripts/jquery.signalR-2.4.1.min.js"></script>  
  13. <script type="text/javascript" src="/signalr/hubs"></script>  
  14. <script type="text/javascript">  
  15. $(function () {  
  16. // Declare a proxy to reference the hub.  
  17. var notifications = $.connection.myHub;  
  18. //debugger;  
  19. // Create a function that the hub can call to broadcast messages.  
  20. notifications.client.updateMessages = function () {  
  21. getAllMessages()  
  22. };  
  23. // Start the connection.  
  24. $.connection.hub.start().done(function () {  
  25. console.log("connection started")  
  26. //notifications.onconn();  
  27. getAllMessages();  
  28. }).fail(function (e) {  
  29. alert(e);  
  30. });  
  31. });  
  32. function getAllMessages() {  
  33. var tbl = $('#messagesTable');  
  34. $.ajax({  
  35. url: '/Home/GetMessages',  
  36. contentType: 'application/html ; charset:utf-8',  
  37. type: 'GET',  
  38. dataType: 'html',  
  39. success: function (result) {  
  40. console.log(result);  
  41. var a2 = JSON.parse(result);  
  42. tbl.empty();  
  43. var i = 1;  
  44. $.each(a2, function (key, value) {  
  45. tbl.append('<tr>' + '<td>' + i + '</td>' + '<td>' + value.empName + '</td>' + '<td>' + value.Salary + '</td>' + '<td>' + value.DeptName + '</td>' + '<td>' + value.Designation + '</td>' +'</tr>');  
  46. i = i + 1;  
  47. });  
  48. }  
  49. });  
  50. }  
  51. </script>  
  52. }  
  53. <div class="container">  
  54. <div class="panel-group">  
  55. <div class="panel panel-default">  
  56. <div class="panel-heading">Employee Information</div>  
  57. <div class="panel-body">  
  58. <div>  
  59. <table id="tab"></table>  
  60. </div>  
  61. <div class="row">  
  62. <div class="col-md-12">  
  63. <div>  
  64. <table class="table table-striped">  
  65. <thead>  
  66. <tr>  
  67. <th>Id</th>  
  68. <th>Name</th>  
  69. <th>Salary</th>  
  70. <th>Department</th>  
  71. <th>Designation</th>  
  72. </tr>  
  73. </thead>  
  74. <tbody id="messagesTable"></tbody>  
  75. </table>  
  76. </div>  
  77. </div>  
  78. </div>  
  79. </div>  
  80. </div>  
  81. </div>  
  82. </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 
  1. [assembly: OwinStartupAttribute(typeof(SignalR.Startup))]  
  2. namespace SignalR  
  3. {  
  4. public class Startup  
  5. {  
  6. public void Configuration(IAppBuilder app)  
  7. {  
  8. // For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=316888  
  9. app.MapSignalR();  
  10. }  
  11. }  
  12. }  
 Step-8
 
Now we insert the record into the table in some interval of times you can see the output. 
  1. DECLARE @i INT = 1;  
  2. DECLARE @sal INT = 78000;  
  3. DECLARE @sal_increment INT = 1;  
  4. WHILE (@i <=20)  
  5. BEGIN  
  6. WAITFOR DELAY '00:00:02'  
  7. set @sal_increment=5000  
  8. /*Your Script*/  
  9. insert into Employee  
  10. select 'Lori_'+cast(@i as varchar),@sal+@sal_increment,'IT','Software Engineer'  
  11. SET @i = @i + 1;  
  12. SET @sal = @sal + @sal_increment;  
  13. 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