Polling for database changes: SqlDependency, SignalR is Good

Question

I need to show changes to data in a database through SqlDependency and SignalR. Suppose my transaction table is often changed by many people. Suppose in a few seconds, the data is changed many times, then I would like to know how notifications will go to the SqlDependency class ?

Will change data be queued before SqlDependency class?

Can the SqlDependency class handle data change when huge number of traffic will do the changes?

I read this article on SqlDependency & SignalR.

A few things were 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 is changed, then the dependency_OnChange event will fire and JobHub.Show(); is called.

JobHub is the name of a class (not a static class), so I'd like to know how to call JobHub.Show(); from outside?

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

See the jQuery code in the view that fetches data and populates a table. The first time when the page loads, suppose there are 5 records existing in the table, so 5 records will be passed to the jQuery code and it will just display those 5 records. But when any existing data will be changed in the table, then what will happen?

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

If you say only the 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 empties the table first and builds the table again.

So my question is, if the data is changed and only changed data will come, then one row should display at client side.... am I right? But in video the change is showing other data as well.

So please read the link article once and then answer my question. 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 the SqlDependency setup method, (and I recommend that you do not), you can call this method whenever 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 case, I would create a separate hub Tracking class like in this example.

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.

Answer 2

SQL Change notification works for "changed data returned by the query". This means - if you query for a job with ID=3 - only changes to this record will trigger the notification.

here are good explanations about this: SqlDependency OnChange Not Firing

The thing is - in the sample the app wants to get informed if "any" record in the table gets changed. This works so far - and (in the link above point 1 in the answer) the event fires ONCE.

To retrigger this again you have to submit the query once more. This is what the sample does - it again fetches all the data.

A possible solution (to achieve what you request) is:

A. trigger the request by a call to get data --you don't have to display the results on the client - just execute the query.

B. when the event fires - make the query again - (not sending the data to the client)

C. you can use an other (extra) query to find out what happened or just use the result from B for this

D.) send the information out with signalR

To get the changes YOU have to find a way to compare the data since the only information SQL Server provides is that "Something happened" (and what) - but not which records were affected.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo(v=vs.110).aspx

The other problem with the sample (I just guess) is that if you have multiple clients listening - each client will "retrigger" again - this could (I'm not sure) in a lot of subscriptions to the event.

So the next time it will fire multiple times - resulting in multiple requests...

So to extend the above sample you should (additional to the above steps):

1.) Change GetData that it does not retrigger again if a subscription is already made

2.) Provide an extra function that informs the clients about the changes --some kind of "seen by clients" flag --a changes clears this flag (trigger) - the notification clears it

3.) change your database schema that you can easily find changed records --some extra table to hold deleted records in the case you will show what was deleted

A different approach (not using SQLChangeNotifications) would be to hold CLR code in the database. This code could call the hub which notifies the clients. But such a solution depends on several factors.

a.) can a signalR client be run inside SQL Server

b.) does your policy allow that the DB server talks to the web server

c.) does your policy allow SQL CLR integration

e.)....

The second approach looks easier for me since you can trigger the send from a SQL CLR Trigger. This enables you to send the changed data without "extra columns" and "deleted element" tables.