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.