SQL Dependency on change event firing based on num

2019-09-18 10:20发布

问题:

I have just tried implementing sql dependency to detect changes in table, as far as implementation I am getting the trigger as soon as I change data in table, but problem is I am getting trigger based on number of connections. For example if I have my url open in 2 browsers then dependency on change event is triggered twice when there is a change in table, if it is opened thrice then three times my on change event is triggered and soon.

Here is the code that I tried.

        private void GetUsers()
        {   
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = "SELECT FirstName, LastName FROM dbo.[Users]";

                    command.Notification = null;

                    //  creates a new dependency for the SqlCommand
                    SqlDependency dependency = new SqlDependency(command);
                    //  creates an event handler for the notification of data
                    //      changes in the database.
                    //  NOTE: the following code uses the normal .Net capitalization methods, though
                    //      the forum software seems to change it to lowercase letters
                    dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange);

                    connection.Open();

                    List<UserInfo> userList = new List<UserInfo>();
                    using (IDataReader userDataReader = command.ExecuteReader())
                    {
                        if (userDataReader != null)
                        {
                            while (userDataReader.Read())
                            {
                                UserInfo userInfo = new UserInfo();
                                userInfo.FirstName = Convert.IsDBNull(userDataReader["FirstName"]) ? string.Empty : Convert.ToString(userDataReader["FirstName"]);
                                userInfo.LastName = Convert.IsDBNull(userDataReader["LastName"]) ? string.Empty : Convert.ToString(userDataReader["LastName"]);
                                userList.Add(userInfo);
                            }
                        }
                    }

                    if (userList != null && userList.Count > 0)
                    {
                        this.dgUserList.DataSource = userList;
                        this.dgUserList.DataBind();
                    }
                }
            }
        }

private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            this.GetUsers();

            // this will remove the event handler since the dependency is only for a single notification
            SqlDependency dependency = sender as SqlDependency;

            //  NOTE: the following code uses the normal .Net capitalization methods, though
            //      the forum software seems to change it to lowercase letters
            dependency.OnChange -= new OnChangeEventHandler(Dependency_OnChange);


            // To broadcast my message
            ChatHub chat = new ChatHub();
            chat.send("myname", "New Registration Done");
        }

Any help would be greatly appreciated.