SqlDependency, vue rapide.

Il m’a été amené récemment à utiliser les SqlDependency dans le cadre d’un de mes projets. Je vous fais donc partager aujourd’hui mon retour sur cette fonctionnalité SQL Server / .Net et en bonus je vous donne un code pour les gérer plus facilement.

Qu’est que SqlDependecy

Avant toute chose, revenons sur cette fonctionnalité. Depuis SQL Server 2005, il est possible de créer une dépendance entre du code .Net (via les APIs ADO.Net) et un jeu de résultat SQL. Pour faire simple, SQL Server va pourvoir nous notifier quand le résultat d’une requête change. Pour cela on lui fournit une requête SELECT simple qu’il va exécuter, le résultat de cette requête servira de référence pour la notification. Ensuite, si ce même SELECT donne un résultat différent plus tard, SQL Server nous rappelle pour nous notifier que le premier résultat obtenu est différent du dernier résultat qu’il a évalué. Très pratique en web pour faire de l’invalidation de cache.

Requête et base de données

Il y a cependant des choses à connaître quand on utilise la SqlDependecy. Par exemple pour la requête qui est donnée pour référence :

• La requête qui est donnée pour référence ne doit pas être avec un SELECT *, il lui faut des noms explicites de colonne.
• La requête qui est donnée pour référence doit indiquer l’utilisateur qui possède la table accédée, par exemple [dbo] pour le cas le plus courant. 

On doit donc avoir des requêtes qui ressemblent à ça : 

SELECT [col1], [col2] FROM [dbo].[MaTable]

Ensuite au niveau de l’instance de la base de données, il faut activer le service BROKER sur la base, si ce n’est pas fait cette commande permettra de l’activer :

ALTER DATABASE [Nom Base] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

Le service BROKER va permettre justement de gérer cette communication entre notre assembly et SQL Server. Une fois cette fonctionnalité activée, passons au code coté .Net.

Implémentation de la SqlDependency

Ci-dessous le code pour implémenter simplement une SqlDependency, je vais l’expliquer par la suite.

private void CreateDependency()
{
  string connectionString = "Mettre ici la chaîne de connexion";

  // Démarrage du broker sur la base
  SqlDependency.Start(connectionString);

  // Création de la connexion
  using (SqlConnection connexion = new SqlConnection(connectionString))
  {
    // Création de la commande pour la requêt de référence
    using (SqlCommand command = new SqlCommand("SELECT col1 FROM [dbo].[MaTable]", connexion))
    {
      command.Notification = null;

      // Création de la dépendance
      SqlDependency dependency = new SqlDependency(command);
      dependency.OnChange += new OnChangeEventHandler(DependencyChange);

      // On démarre la dépendance
      command.ExecuteReader();
    }

    // Fermeture de la connexion
    connexion.Close();
  }

  // Arrêt du broker sur la base
  SqlDependency.Stop(connectionString);
}

private void DependencyChange(object sender, SqlNotificationEventArgs e)
{
  // On se désabonne
  (sender as SqlDependency).OnChange -= new OnChangeEventHandler(DependencyChange);

  // Code de traitement ici
}

Ici on peut voir comment se structure la création de la SqlDependency. On doit d’abord démarrer le BROKER de la base, afin qu’il écoute les dépendances, ensuite on exécute une requête ADO.Net classique, à savoir l'instanciation d’une connexion, la création d’une commande et l'exécution de celle-ci.

La création de la dépendance se situe entre la création de la commande et son exécution. On y crée alors la dépendance sur la commande et on s’abonne à l’événement OnChange pour récupérer la notification de changement.

La dépendance n’est créée qu’au moment où la commande est exécutée, le résultat qu’elle produit est la référence SQL Server pour savoir si un changement a été fait.

Ensuite c’est très simple, dès que le résultat de la requête de dépendance change, la fonction de retour (ici DependencyChange) est appelé, il n’y a plus qu’à faire les traitements qui nous intéresses. 

Par contre, il faut bien comprendre une chose, une SqlDependency ne fonctionne qu’une seule fois ! Une fois qu’elle est traitée (en gros que la fonction de retour est invoqué) elle disparait du serveur SQL Server et ne fonctionnera plus. En gros, c’est à usage unique.

Il est donc impossible de créer une SqlDependency qui va invoquer la fonction de retour autant de fois que le résultat de la requête de dépendance est modifié. Pour pouvoir boucler sur les modifications, il faut recréer une autre dépendance.

Dans cette optique, il n’est donc pas nécessaire de conserver les instances des commandes, connexions et autre objets utilisés pour la création de la dépendance. C’est pour cela que dans le code ci-dessus, tout est libéré par des 'using'.

Manager de dépendance

Vous pouvez télécharger plus bas, deux classes qui permettent de gérer des SqlDependency de façon plus automatique et de boucler sur les notifications. J’ai créé ces classes dans le but de factoriser la gestion des SqlDependency par chaîne de connexion. Pour les utiliser, il faut ajouter l’assembly 'System.Configuration', voici le détail des deux classes :

DependencyManagerItem.cs (8,22 kb)

Cette classe gère une dépendance simple, de façon atomique. On peut l’utiliser seule, mais elle n’active pas le BROKER coté base de données. Le constructeur est classique, on y renseigne la chaine de connexion, une fonction de retour et si on active le mode continue (qui recrée la SqlDependency automatiquement).

Cette classe, possède ensuite une propriété 'Datas' qui renvoi l’état du résultat de la requête de référence, ce qui permet de l’avoir sans avoir à le requêter à nouveau. Ensuite, pour démarrer la dépendance il suffit d’invoquer la méthode 'Start()', si vous êtes dans un mode boucle, la méthode 'Stop()' permet d’arrêter la dépendance.

DependencyManager.cs (5,58 kb)

Le principe de cette classe est de gérer de façon plus globale les dépendances. On l’instancie avec une chaine de connexion et elle va démarrer le BROKER de la base, mais aussi l’arrêter quand elle sera recyclée.

Elle possède des propriétés qui lui permettent de démarrer des dépendances avec des valeurs par défaut (il est toujours possible de démarrer une dépendance sans ses valeurs par défaut).

Pour démarrer une dépendance, il faut invoquer la méthode 'Create()' qui prend en paramètre un nom, ce nom servira à identifier la dépendance pendant tout son cycle de vie dans le Manager. Ensuite il y deux versions de 'Create()', une qui crée la dépendance avec les paramètres par défaut, et l’autre pour les outrepasser. Pour finir, une méthode 'Remove()' mettra fin à une dépendance nommée.

Ces classes sont faciles d’utilisation et vous avez le code, donc libre à vous de les étudier et/ou de les adapter à vos besoins.