jquery - Polling for database changes: SqlDependency, SignalR is Good

Question

it will be good if i need to show change data in db through sql dependency and signalr. suppose my transaction table often changed by many people. suppose in few second data is change many time then i like to know how notification will go to sql dependency class ?

change data will be queued before sql dependency class ? sql dependency class can handle data change when huge no of traffic will do the changes ?

here i was reading a article on SqlDependency & SignalR. the link is http://techbrij.com/database-change-notifications-asp-net-signalr-sqldependency

few things was not clear to me.

1. how to give subscribe query notification permission to IIS ?
2. please see the line in this article.

.

private void dependency_OnChange(object sender, SqlNotificationEventArgs e) {
    JobHub.Show();
}

when data will change then dependency_OnChange event will fire and JobHub.Show(); is calling

JobHub is name of class and not static class so i like to know how anyone can call `JobHub.Show();` from out side ??

4. what is GlobalHost class and when it is used ?
5. the article code related issue. just go to this link

http://techbrij.com/database-change-notifications-asp-net-signalr-sqldependency

see the jquery code in view which fetch data and populate table. first time when page load suppose there is 5 records exist in table so 5 records will be passed to jquery code and it will just display those 5 records but when any existing data will be change in table then what will happen ??

the only changed rows will come to client side or all data including changed data will come to client side ?

if you say only changed data will code then just see the video in that link. it is shown in the video data is changed one by one and change is reflecting at client end but if you see the jquery code it just empty the table first and build the table again. so my question is if data change and only change data will come then one row should display at client side.... am i right. but in video the change is showing as well as other data is too.

so please for good sake read the link article once and then answer my question. thanks

please guide me. thanks

Answer 1

Ok, ManniAT is close, but not quite on the mark, what is going on here is that SQLDependency notification events are a one shot deal. So as he points out it will fire the first time. You need to remove that handler (this prevents the multi-fire scenario when you make a subsequent call to the method) and re-add it so that it will fire again. If you do not want to return your data directly from SQLDependency setup method, (And I recommend that you do not) You can call this method when ever you need to reestablish your listener.

Answers: 1) Once the notification fires, you call a method on the Hub that refreshes the data that has changed. SqlDependency Notifications should be as specific as possible, and when it fires you should already know what part of the UI needs to update.

2) You did not set your Hub as a static class, and therefore you cannot call methods on the class without first instantiating an instance of the class before calling the show method, in the example I believe it is a static class so that is why it works. Making a hub a static class is not what I would recommend in this care, I would create a separate hub Tracking class like in this example. http://www.asp.net/signalr/overview/signalr-20/getting-started-with-signalr-20/tutorial-server-broadcast-with-signalr-20

3)GlobalHost File in this case I believe is your Global.asax where you start and stop your SqlDependencies.

Modification to the example:

try
        {
            using (
                var connection =
                    new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(@"SELECT [Id]
                                                                        ,[FName]
                                                                        ,[LName]
                                                                        ,[DOB]
                                                                        ,[Notes]
                                                                        ,[PendingReview] 
                                                       FROM [dbo].[Users]",
                    connection))
                {
                    // Make sure the command object does not already have
                    // a notification object associated with it.
                    command.Notification = null;

                    SqlDependency dependency = new SqlDependency(command);

                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    command.ExecuteReader();
                }
            }
        }
        catch (Exception e)
        {
            throw;
        }
    }

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{   
    SqlDependency dependency = sender as SqlDependency;
    if (dependency != null) dependency.OnChange -= dependency_OnChange;
    //Recall your SQLDependency setup method here.
    SetupDependency();
    JobHub.Show();
}

I hope this helps you! If you have any more questions let me know.