In this tutorial we will learn how to use SignaR in MVC application and also see how it works on real-time data push with SQL Server. If you are new to SignalR then must learn what is SignalR and where to use SignalR, please go through the link https://www.coreprogramm.com/2019/12/what-is-signalr.html.

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]
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
    @{  
       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>  
Code Explanation >>
>> 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.


</> 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

Previous Post Next Post