SqlDependency, vue rapide.

by Nicolas Calvi 14. octobre 2011 11:54

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.

Définir une source de donnée WebService (SSRS)

by Nicolas Calvi 16. avril 2009 18:11

Je vais aborder ici comment faire pour définir une source de donnée de type WebService (XML) pour l'utiliser dans un rapport Reporting Services 2005. La chose se fait en deux phases.

Phase 1 : Définition de la source de donnée

Il est évident que dans ce cas, la source de donnée sera l'adresse de notre WebService, pour cela créez votre source de donnée et paramétrez là comme ceci : 

Il faut que le retour de votre WebService soit un XML ou un objet sérialisable dans ce sens. Une fois la source de donnée définie, il n'y a plus qu'à l'utiliser dans un DataSet.

Phase 2 : Définition d'un DataSet avec la source de donnée WebService

Le DataSet se défini comme normalement, on lui donne un nom, on lui donne comme source de donnée notre source de type XML. La seule chose a savoir est comment récupérer les données du WebService.

C'est dans la QueryString que l'on doit spécifier toutes ses informations, sous forme d'un XML propre à Reporting Services

Le XML (ci-bas) permet de définir la méthode su WebService à invoquer, ansi que les informations nécéssaire à la récupération des données.

<Query>
  <Method Namespace="http://tempuri.org/" Name="MaMethode"/>
  <SoapAction>http://tempuri.org/SuiviChantier</SoapAction>
  <ElementPath IgnoreNamespaces="true">
    *
  </ElementPath>
</Query>

 

Si le le WebService prend des paramètres, il suffit d'aller sur l'onglet Parameters et de les définirs. Il est alors possible de définir des paramètres en dur ou des paramètres passés au rapport.

Une fois terminé, vous n'avez plus qu'a utiliser le DataSet dans votre rapport. Mais il arrive parfois que Reporting Services ne remplisse pas automatiquement la liste des champs de binding, à ce moment vous devrez le faire à la main en ajoutant les champs un à un. Même si il ne les a pas trouvé en définissant le DataSet, cela ne l'empêchera pas de les récupérer par la suite.

Execution fichier script SqlServer

by Nicolas Calvi 4. mars 2009 13:32

Récemment j'ai été confronté à une problématique étrange, l'impossibilité d'exécuter des fichiers sql dans d'autre fichier sql avec SqlServer. En effet venant du monde Oracle, je pensais bêtement qu'il y avait un équivalent au '@' pour l'exécution de fichier.

Après de nombreuses investigations, je me suis aperçu que la seule façon de faire était de passer en mode SqlCommand (SqlCmd). Car une fois passé dans ce mode on peut lancer un série de fichier script avec le mot clé ":r".

:r C:\Scripts\MonScript_01.sql

:r C:\Scripts\MonScript_02.sql

De plus on peut activer l'erreur sur la première erreur via l'instruction ":On Error exit", ce qui permet un comportement similaire à Oracle avec arrêt en cas de problème.