locked
SignalR Dependency Onnchange Methods executes More than one time when one row in updated RRS feed

  • Question

  • User611346789 posted

    Hello all,

    I am using SignalR for update my Employee Table in real time and Want to send notification for all client. but when i changed to my database then SqlDependency_Onchnge event Fires more than one time and and program behaves wired like at the time of debug its forward or backward line executes and creates sometimes infinite loop. For this problem I can't add notification and this creates more problem on multiple Event handle on Table. It makes slower browser also.

    Can you please suggest me how can i solve my problem. I submit my Code Below

    Controller CODE:

    public class HomeController : Controller
        {
            private string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            public ActionResult Index()
            {
                return View();
            }
            [HttpGet]
            public JsonResult GetEmployee()
            {
                Employee emp = new Employee();
                List<Employee> emplIst = new List<Employee>();
                using (SqlConnection con = new SqlConnection(CS))
                {
                    con.Open();
                    string Command = @"SELECT  [ID],[Name],[Email],[Gender],[Age] FROM [dbo].[Employee]";
                    using (SqlCommand cmd = new SqlCommand(Command,con))
                    {
                        cmd.Notification = null;
    
                        if (con.State == ConnectionState.Closed)
                        {
                            con.Open();
                        }
    
                        SqlDependency dep = new SqlDependency(cmd);
                        dep.OnChange += dep_OnChange;
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            emp = new Employee();
                            emp.ID = (int)reader["ID"];
                            emp.Name =reader["Name"].ToString();
                            emp.Email =reader["Email"].ToString();
                            emp.Gender = reader["Gender"].ToString();
                            emp.Age = (int)reader["Age"];
    
                            emplIst.Add(emp);
                        }
    
                    }
                }
                return Json(emplIst, JsonRequestBehavior.AllowGet);
            }
    
            void dep_OnChange(object sender, SqlNotificationEventArgs e)
            {
                if (e.Type == SqlNotificationType.Change)
                {
                    //SqlDependency dep = new SqlDependency();
                    //dep.OnChange -= dep_OnChange;
                    EmployeeHubs.loadEmployee();
                }
            }
        }

    HUB Class CODE:

    [HubName("employeeHub")]
        public class EmployeeHubs : Hub
        {
            //public void Hello()
            //{
            //    Clients.All.hello();
            //}
            [HubMethodName("loadEmployee")]
            public static void loadEmployee()
            {
                IHubContext context = GlobalHost.ConnectionManager.GetHubContext<EmployeeHubs>();
                context.Clients.All.loadAllEmployee();
            }
        }

    HTML CODE:

    @model Demo_SignalR.Models.Employee
    
    @{
        Layout = null;
    }
    
    <!DOCTYPE html>
    
    <html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>Index</title>
        <link href="~/Content/bootstrap.min.css" rel="stylesheet"/>
        <link href="~/Content/bootstrap-theme.min.css" rel="stylesheet" />
    </head>
    <body>
    <div class="row">
        <div class="col-md-12">
            <table class="table table-hover table-bordered" id="EmployeeTable">
                <thead>
                <tr>
                    <td>ID</td>
                    <td>Name</td>
                    <td>Email</td>
                    <td>Gender</td>
                    <td>Age</td>
                </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
    </div>
        <script src="~/Scripts/jquery-1.9.1.min.js"></script>
        <script src="~/Scripts/bootstrap.min.js"></script>
    <script src="~/Scripts/jquery.signalR-2.2.1.min.js"></script>
    <script src="~/SignalR/hubs"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            var EmployeeLoads = $.connection.employeeHub;
            EmployeeLoads.client.loadAllEmployee = function () {
                getEmployees();
            }
            $.connection.hub.start();
                console.log("Connection Started");
                getEmployees();
           
        });
        function getEmployees() {
            $.ajax({
                url: '@Url.Action("GetEmployee","Home")',
                type: 'GET',
                dataType: 'JSON',
                contentType: 'application/json ; charset:utf-8',
                success: function (data) {
                    $('#EmployeeTable tbody').empty();
                    var table = $('#EmployeeTable tbody');
                    
                    var row = '';
                    for (var i = 0; i < data.length; i++) {
                        console.log(data[i].ID);
                        row += '<tr>' +
                            '<td>' + data[i].ID + '</td>' +
                            '<td>' + data[i].Name + '</td>' +
                            '<td>' + data[i].Email + '</td>' +
                            '<td>' + data[i].Gender + '</td>' +
                            '<td>' + data[i].Age + '</td>' +
                            '</tr>';
                    }
                    table.append(row);
                },
                error: function(xhr) {
                    alert(xhr.responseText);
                }
            });
        }
    </script>
    </body>
    </html>
    

     

    Saturday, February 11, 2017 6:05 PM

All replies