Post
Ask Question

Showing Data Changes Using SQLDependency With SignalR In ASP.NET MVC

Here, I am using SQLDependency which notifies us when any changes are done to the data in the database. If you don't know about SQLDependency, refer to the link shown below.
 
 
First, you have to add an empty MVC project and after that, add the SignalR Package which can be added by using NuGet Package Manager.
 
Add a Controller in your project.
  1. public class HomeController : Controller  
  2.     {  
  3.         // GET: Home  
  4.         public ActionResult Index()  
  5.         {  
  6.             return View();  
  7.         }  
  8.         public JsonResult GetMessages()  
  9.         {  
  10.             List<data> messages = new List<data>();  
  11.             Repository r = new Repository();  
  12.             messages = r.GetAllMessages();  
  13.             return Json(messages,JsonRequestBehavior.AllowGet);  
  14.         }  
  15.     } 
Now, add a new class and name it as Repository.
  1. public class Repository  
  2.     {  
  3.         SqlConnection co = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["con"].ConnectionString);  
  4.         public List<data> GetAllMessages()  
  5.         {  
  6.             var messages = new List<data>();  
  7.             using (var cmd = new SqlCommand(@"SELECT [id],   
  8.                 [name],[adres] FROM [dbo].[userdata]", co))  
  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 data  
  18.                     {  
  19.                         id = int.Parse(ds.Tables[0].Rows[i][0].ToString()),  
  20.                         name = ds.Tables[0].Rows[i][1].ToString(),  
  21.                         adres = ds.Tables[0].Rows[i][2].ToString()  
  22.                     });  
  23.                 }  
  24.             }  
  25.             return messages;  
  26.         }  
  27.   
  28.         private void dependency_OnChange(object sender, SqlNotificationEventArgs e) //this will be called when any changes occur in db table. 
  29.         {  
  30.             if (e.Type == SqlNotificationType.Change)  
  31.             {  
  32.                 MyHub.SendMessages();  
  33.             }  
  34.         }  
  35.     } 
In Models folder, add a class and name it as data.
  1. public class data  
  2.    {  
  3.        public int id { get; set; }  
  4.        public string name { get; set; }  
  5.        public string adres { get; set; }  
  6.    } 
Here, you have to add a SignalR Hub Class to you project and name it as MyHub.
  1. [HubName("MyHub")]  
  2.    public class MyHub : Hub  
  3.    {  
  4.   
  5.        [HubMethodName("sendMessages")]  
  6.        public static void SendMessages()  
  7.        {  
  8.            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<MyHub>();  
  9.            context.Clients.All.updateMessages();  
  10.        }  
  11.    } 
Again, you have to add another class named OWIN Startup Class.
  1. public class Startup  
  2.    {  
  3.        public void Configuration(IAppBuilder app)  
  4.        {  
  5.           // For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=316888  
  6.          app.MapSignalR();  
  7.        }  
  8.    } 
View(Index) 
  1. @{  
  2.     Layout = null;  
  3. }  
  4.   
  5. <!DOCTYPE html>  
  6.   
  7. <html>  
  8. <head>  
  9.     <meta name="viewport" content="width=device-width" />  
  10.     <title>Index</title>  
  11.     <script type="text/javascript" src="~/Scripts/jquery-1.6.4.min.js"></script>  
  12.     <script type="text/javascript" src="~/Scripts/jquery.signalR-2.2.2.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.   
  19.         //debugger;  
  20.         // Create a function that the hub can call to broadcast messages.  
  21.         notifications.client.updateMessages = function () {  
  22.             getAllMessages()  
  23.   
  24.         };  
  25.         // Start the connection.  
  26.         $.connection.hub.start().done(function () {  
  27.             alert("connection started")  
  28.             //notifications.onconn();  
  29.             getAllMessages();  
  30.         }).fail(function (e) {  
  31.             alert(e);  
  32.         });  
  33.     });  
  34.   
  35.     function getAllMessages()  
  36.     {  
  37.         var tbl = $('#messagesTable');  
  38.         $.ajax({  
  39.             url: '/Home/GetMessages',  
  40.             contentType: 'application/html ; charset:utf-8',  
  41.             type: 'GET',  
  42.             dataType: 'html'  
  43.         }).success(function (result) {  
  44.             var a2 = JSON.parse(result);  
  45.             tbl.empty();  
  46.             $.each(a2, function (key, value)  
  47.             {  
  48.                 tbl.append('<tr>' + '<td>' +value.id + '</td>' + '<td>' + value.name + '</td>' + '<td>' + value.adres + '</td>' + '</tr>');  
  49.             });  
  50.         })  
  51.     }  
  52.     </script>  
  53. </head>  
  54. <body>  
  55.     <div>   
  56.         <table id="tab">  
  57.              
  58.         </table>  
  59.     </div><br/>  
  60.     <div class="row">  
  61.         <div class="col-md-12">  
  62.             <div >  
  63.                 <table border="1">  
  64.                     <thead>  
  65.                         <tr>  
  66.                             <th>id</th>  
  67.                             <th>name</th>  
  68.                             <th>address</th>  
  69.                         </tr>  
  70.                     </thead>  
  71.                     <tbody id="messagesTable">  
  72.   
  73.                     </tbody>  
  74.                 </table>  
  75.             </div>  
  76.         </div>  
  77.     </div>  
  78. </body>  
  79. </html>   
Next Recommended Reading Resolved: SignalR Error: Failed To Complete Negotiation With The Server