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

| Loick DESTIN (soat) | soat |

SQL Server est un système de gestion de base de données « Made In Microsoft » très complet qui offre l'accès à plusieurs services. Ceux qui vont nous intéresser ici, sont ceux qui vont permettre à notre base de données de notifier nos applications de ses changements.

En effet, grâce à certains services dont on va parler dans cet article, il est possible d'envoyer des messages à nos applications, mais aussi de créer des requêtes de notification, on y reviendra plus tard. Commençons par le « Service Broker ».

Commentez

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

- Auteurs : Loick DESTIN (soat), soat

- Parution : 14 mai 2014

- Mise à jour : 21 octobre 2014

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2014 Zied BEN TAHAR. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Qu'est-ce que le « Service Broker » ?

- II. SQL Server et les notifications

- III. Gérer les notifications de notre base de données SQL Server

- III-A. Première étape : s'abonner aux notifications

- III-B. Deuxième étape : créer sa requête de notification

- III-C. Troisième étape : gestion des notifications

- IV. Conclusion

- V. Remerciements

I - Qu'est-ce que le « Service Broker » ?

Pour faire court, « Service Broker » est un service permettant aux instances SQL Server de communiquer entre elles de manière sûre et efficace. C'est aussi ce service qui permet l'activation des notifications de requêtes.

Pour l'activer dans notre base de données, il suffit d'exécuter cette commande T-SQL :

ALTER DATABASE Notre_DB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Une fois la commande exécutée, la base de données est prête à communiquer avec d'autres instances de SQL Server comme celles que l'on trouve dans nos applications.

Il ne reste plus qu'à demander à la base de données de nous notifier après la modification de ses données. On va enfin toucher à du .Net.

II - SQL Server et les notifications

Avec SQL Server, il y a trois manières de gérer les notifications dans nos applications .Net :

- en utilisant SqlNotificationRequest : on peut faire une gestion des notifications bas niveau, donc assez personnalisable avec différentes options. (En plus d'activer le « Service Broker », il faut créer des QUEUE et des SERVICE dans notre base de données) ;

- en utilisant SqlDependency : on fait abstraction de toute la complexité bas niveau des fonctionnalités de notification et on détecte facilement les modifications qui sont faites sur nos bases de données ;

- en utilisant SqlCacheDependency : on utilise implicitement un SqlDependency pour tout ce qui concerne la gestion de cache dans les applications ASP.Net.

Vous l'aurez compris, dans cet article, on va se concentrer sur SqlDependency pour notifier simplement et efficacement nos applications. Cela se fait rapidement et en quelques étapes.

III - Gérer les notifications de notre base de données SQL Server

Pour éviter toute confusion et pour vous donner des aperçus du programme en une image, je ferai simple en faisant mon exemple sur une application console.

III-A - Première étape : s'abonner aux notifications

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) :

1. SqlDependency.Start(MyConnectionString);

2. /* …Mon super code… */

3. SqlDependency.Stop(MyConnectionString);

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

III-B - Deuxième étape : créer sa requête de notification

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 :

1. private const string MyFirstNotificationRequest = @"SELECT MyColumn

2. FROM dbo.MyTable

3. WHERE MyCondition = TRUE";

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

1. static void MyExecution()

2. {

3. using (SqlConnection connection = new SqlConnection(MyConnectionString))

4. {

5. connection.Open();

7. using (SqlCommand command = new SqlCommand(MyFirstNotificationRequest, connection))

8. {

9. 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)

10. // Notez que pour s'abonner à une commande, on peut aussi passer par la méthode AddCommandDependency()

12. using (SqlDataReader reader = command.ExecuteReader()) // C'est ici que se fait l'inscription

13. {

14. /* … Traitement des données … */

15. }

16. }

17. }

18. }

À 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.

III-C - Troisième étape : gestion des notifications

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 :

1. dependency.OnChange += dependency_OnChange;

Puis :

1. static void dependency_OnChange(object sender, SqlNotificationEventArgs e)

2. {

3. /* ... Gestion de la notification ... */

4. }

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 :

IV - Conclusion

Vous savez maintenant comment abonner vos applications à votre base de données, ce qui pourra vous permettre de synchroniser vos données entre les serveurs et/ou les clients ou d'être alerté lors d'un changement/problème lié à la base de données. On peut s'en servir pour mettre à jour automatiquement le cache ou rafraîchir les vues qui sont du coté client automatiquement (en utilisant Signal R pour les applications en ASP.Net).

V - Remerciements

L'équipe de la rédaction .NET remercie sincèrement Soat de nous avoir autorisés à publier cet article sur Developpez.com. Soat est une société de conseil spécialisée dans l'accompagnement de ses clients, tout au long du cycle de vie de leurs projets, et le développement de technologies Java et Web.

Nous remercions également Vincent VIALE pour la mise au gabarit et Malick SECK pour sa relecture orthographique.

N'hésitez pas à donner vos avis sur ce tutoriel dans le forum : Commentez