Comment notifier les changements d'une base de données SQL Server à son application .Net

Pour que notre application s'abonne aux notifications venant de la base de données dans laquelle on a activé le « Service Broker » (oui, il ne faut pas l'oublier), il faut commencer par exécuter la méthode Start() de SqlDependency et terminer l'application par la méthode Stop() (généralement en début et fin d'exécution de l'application) :

SqlDependency.Start(MyConnectionString);
/* …Mon super code… */
SqlDependency.Stop(MyConnectionString);

Donc pour l'instant, mon code ressemble à ça :

Une requête de notification, c'est une requête simple (SELECT ou EXEC) qui va nous retourner des données sur une table (pas de JOIN, pas de VIEW, pas de…). C'est la requête qui va être effectuée à chaque modification dans la base. Lorsqu'il y a une différence de résultat entre deux exécutions de la requête qui se suivent, une notification est envoyée à nos applications inscrites à cette requête.

Pour s'inscrire à ces notifications, c'est simple. Il suffit d'exécuter une commande comme on le fait habituellement pour exécuter des requêtes normales. Les seules différences sont les limitations sur le SELECT de notre requête, et le fait que l'on doit lier l'instance de SqlCommand qui va exécuter la requête à une instance de SqlDependency. Si en exécutant votre code, vous avez l'impression que votre application essaye de s'inscrire en boucle sans être notifiée des changements, c'est que votre requête ne respecte pas l'une des limitations (exemple : ne pas avoir mis dbo avant le nom de votre table).

Je pense que ce sera plus simple en langage codé :p

Donc ça, c'est une requête de notification :

private const string MyFirstNotificationRequest = @"SELECT MyColumn
                                                    FROM dbo.MyTable
                                                    WHERE MyCondition = TRUE";

Et ça, c'est une inscription à cette requête de notification :

static void MyExecution()
{
     using (SqlConnection connection = new SqlConnection(MyConnectionString))
     {
         connection.Open();
 
         using (SqlCommand command = new SqlCommand(MyFirstNotificationRequest, connection))
         {
             SqlDependency dependency = new SqlDependency(command); // Attention : il faut que command soit lié à une instance de SqlConnection (ici, je l'ai fait dans le constructeur de SqlCommand)
                                                                    // Notez que pour s'abonner à une commande, on peut aussi passer par la méthode AddCommandDependency()
 
             using (SqlDataReader reader = command.ExecuteReader()) // C'est ici que se fait l'inscription
             {
                 /* … Traitement des données … */
             }
         }
     }
}

À chaque fois que les données ciblées par les conditions de la requête vont changer (par un ajout, une modification ou une suppression), l'instance de SqlDependency liée à la commande le saura et c'est là que l'on passe à la 3e et dernière étape.

On a dit précédemment que l'instance de SqlDependency savait quand une notification était envoyée. En fait, un évènement est levé à ce moment. C'est l'évènement OnChange que l'on peut retrouver sur le SqlDependency.

Il suffit d'ajouter cette ligne après l'instanciation du SqlDependency :

dependency.OnChange += dependency_OnChange;

Puis :

static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    /* ... Gestion de la notification ... */
}

Notez que l'application et l'instance de SqlDependency se désabonnent à chaque fois qu'une notification est traitée. Il faut donc se réabonner si on veut une connexion permanente (dans le Handler de l'évènement par exemple).

En s'y abonnant, on a accès à un argument d'évènements de type SqlNotificationEventArgs qui expose une propriété Type de type SqlNotificationType qui représente la cause de la notification (abonnement, modification ou autres), une propriété Source qui va nous dire ce qui a causé la notification (par exemple Data si les données ont changé), une propriété Info qui va nous donner des détails sur ce qui a amené le serveur à nous notifier (insertion/modification/suppression dans le cas d'un changement par exemple). Pour avoir toute la liste des valeurs données par la propriété Info ou Source, je vous invite à regarder les types SqlNotificationType et SqlNotificationSource.

Voici le code final, il va lister la colonne MyColumn de la table MyTable à chaque fois qu'il y a une suppression/modification/création de données dans la base MyDatabase :