Polling for database changes: SqlDependency, Signa

问题:

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

回答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.

回答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.