Partager via


Création d’un assembly de règle Code Analysis statique pour SQL Server

Cette procédure pas à pas montre les étapes permettant de créer une règle d’analyse du code SQL Server. La règle créée lors de cette procédure pas à pas sert à éviter la présence d’instructions WAITFOR DELAY dans les procédures stockées, les déclencheurs et les fonctions.

Lors de cette procédure pas à pas, vous créez une règle personnalisée pour l’analyse statique du code Transact-SQL en effectuant les tâches suivantes :

  1. Créer une bibliothèque de classes, activer la signature pour ce projet et ajouter les références nécessaires.

  2. Créer une classe de règle personnalisée C#.

  3. Créer deux classes d’assistance C#.

  4. Copier la DLL résultante dans le répertoire Extensions pour l’installer.

  5. Vérifier que la nouvelle règle Code Analysis est en place.

Prérequis

Vous devez disposer des éléments suivants pour exécuter cette procédure pas à pas :

  • Une version de Visual Studio qui inclut SQL Server Data Tools et qui prend en charge le développement .NET Framework en C# ou Visual Basic doit être installée.

  • Un projet SQL Server qui contient des objets SQL Server.

  • Instance de SQL Server dans laquelle vous pouvez déployer un projet de base de données.

Cette procédure pas à pas est destinée aux utilisateurs qui sont déjà familiarisés avec les fonctionnalités SQL Server de SQL Server Data Tools. Vous devez être familiarisé avec les concepts de Visual Studio, comme la création d’une bibliothèque de classes, l’ajout de packages NuGet et l’utilisation de l’éditeur de code pour ajouter du code à une classe.

Créer une règle Code Analysis personnalisée pour SQL Server

Créez d’abord une bibliothèque de classes. Pour créer un projet de bibliothèque de classes :

  1. Créez un projet de bibliothèque de classes C# (.NET Framework) ou Visual Basic (.NET Framework) nommé SampleRules.

  2. Renommez le fichier Class1.cs en AvoidWaitForDelayRule.cs.

  3. Dans l’Explorateur de solutions, cliquez avec le bouton droit sur le nœud du projet, puis sélectionnez Ajouter et Référence.

  4. Sélectionnez System.ComponentModel.Composition sous l’onglet Assemblées\Cadres.

  5. Dans l’Explorateur de solutions, faites un clic droit sur le nœud du projet, puis sélectionnez Gérer les packages NuGet. Recherchez et installez le package NuGet Microsoft.SqlServer.DacFx. La version sélectionnée doit être 162.x.x (par exemple 162.2.111) avec Visual Studio 2022.

Ensuite, ajoutez des classes de prise en charge qui seront utilisées par la règle.

Créer les classes de prise en charge de la règle Code Analysis personnalisée

Avant de créer la classe pour la règle proprement dite, ajoutez une classe Visitor et une classe d’attributs au projet. Ces classes peuvent être utiles pour la création de règles personnalisées supplémentaires.

Définir la classe WaitForDelayVisitor

La première classe que vous devez définir est la classe WaitForDelayVisitor, dérivée de TSqlConcreteFragmentVisitor. Cette classe fournit l’accès aux instructions WAITFOR DELAY dans le modèle. Les classes Visitor utilisent les API ScriptDom fournies par SQL Server. Dans cette API, le code Transact-SQL est représenté sous la forme d’une arborescence de syntaxe abstraite et les classes Visitor peuvent être utiles quand vous souhaitez rechercher des objets de syntaxe spécifiques, tels que des instructions WAITFOR DELAY. Ces instructions peuvent être difficiles à trouver à l’aide du modèle objet, car elles ne sont associées à aucune relation ou propriété d’objet spécifique. Toutefois, il est facile de les trouver à l’aide du modèle Visitor et de l’API ScriptDom.

  1. Dans l’Explorateur de solutions, sélectionnez le projet SampleRules.

  2. Dans le menu Projet, sélectionnez Ajouter une classe. La boîte de dialogue Ajouter un nouvel élément s’affiche.

  3. Dans la zone de texte Nom, tapez WaitForDelayVisitor.cs, puis sélectionnez le bouton Ajouter. Le fichier WaitForDelayVisitor.cs est ajouté au projet dans l’Explorateur de solutions.

  4. Ouvrez le fichier WaitForDelayVisitor.cs et mettez à jour le contenu pour qu’il corresponde au code suivant :

    using System.Collections.Generic;
    using Microsoft.SqlServer.TransactSql.ScriptDom;
    namespace SampleRules {
        class WaitForDelayVistor {}
    }
    
  5. Dans la déclaration de classe, définissez « internal » comme modificateur d’accès et dérivez la classe de TSqlConcreteFragmentVisitor :

    internal class WaitForDelayVisitor : TSqlConcreteFragmentVisitor {}
    
  6. Ajoutez le code suivant pour définir la variable membre List :

    public IList<WaitForStatement> WaitForDelayStatements { get; private set; }
    
  7. Définissez le constructeur de classe en ajoutant le code suivant :

    public WaitForDelayVisitor() {
       WaitForDelayStatements = new List<WaitForStatement>();
    }
    
  8. Substituez la méthode ExplicitVisit en ajoutant le code suivant :

    public override void ExplicitVisit(WaitForStatement node) {
       // We are only interested in WAITFOR DELAY occurrences
       if (node.WaitForOption == WaitForOption.Delay)
          WaitForDelayStatements.Add(node);
    }
    

    Cette méthode visite les instructions WAITFOR dans le modèle et ajoute celles qui ont l’option DELAY spécifiée à la liste d’instructions WAITFOR DELAY. La principale classe référencée est WaitForStatement.

  9. Dans le menu File (Fichier), sélectionnez Save (Enregistrer).

Définir la classe LocalizedExportCodeAnalysisRuleAttribute

La seconde classe est LocalizedExportCodeAnalysisRuleAttribute.cs. Il s’agit d’une extension du Microsoft.SqlServer.Dac.CodeAnalysis.ExportCodeAnalysisRuleAttribute intégré fourni par le cadre et qui prend en charge la lecture de DisplayName et de Description utilisés par votre règle à partir d’un fichier de ressources. Cette classe est utile si jamais vous souhaitez utiliser vos règles dans plusieurs langues.

  1. Dans l’Explorateur de solutions, sélectionnez le projet SampleRules.

  2. Dans le menu Projet, sélectionnez Ajouter une classe. La boîte de dialogue Ajouter un nouvel élément s’affiche.

  3. Dans la zone de texte Nom, tapez LocalizedExportCodeAnalysisRuleAttribute.cs, puis sélectionnez le bouton Ajouter. Le fichier est ajouté au projet dans l’Explorateur de solutions.

  4. Ouvrez le fichier et mettez à jour le contenu pour qu’il corresponde au code suivant :

    using Microsoft.SqlServer.Dac.CodeAnalysis;
    using System;
    using System.Globalization;
    using System.Reflection;
    using System.Resources;
    
    namespace SampleRules
    {
    
        internal class LocalizedExportCodeAnalysisRuleAttribute : ExportCodeAnalysisRuleAttribute
        {
            private readonly string _resourceBaseName;
            private readonly string _displayNameResourceId;
            private readonly string _descriptionResourceId;
    
            private ResourceManager _resourceManager;
            private string _displayName;
            private string _descriptionValue;
    
            /// <summary>
            /// Creates the attribute, with the specified rule ID, the fully qualified
            /// name of the resource file that will be used for looking up display name
            /// and description, and the Ids of those resources inside the resource file.
            /// </summary>
            public LocalizedExportCodeAnalysisRuleAttribute(
                string id,
                string resourceBaseName,
                string displayNameResourceId,
                string descriptionResourceId)
                : base(id, null)
            {
                _resourceBaseName = resourceBaseName;
                _displayNameResourceId = displayNameResourceId;
                _descriptionResourceId = descriptionResourceId;
            }
    
            /// <summary>
            /// Rules in a different assembly would need to overwrite this
            /// </summary>
            /// <returns></returns>
            protected virtual Assembly GetAssembly()
            {
                return GetType().Assembly;
            }
    
            private void EnsureResourceManagerInitialized()
            {
                var resourceAssembly = GetAssembly();
    
                try
                {
                    _resourceManager = new ResourceManager(_resourceBaseName, resourceAssembly);
                }
                catch (Exception ex)
                {
                    var msg = String.Format(CultureInfo.CurrentCulture, RuleResources.CannotCreateResourceManager, _resourceBaseName, resourceAssembly);
                    throw new RuleException(msg, ex);
                }
            }
    
            private string GetResourceString(string resourceId)
            {
                EnsureResourceManagerInitialized();
                return _resourceManager.GetString(resourceId, CultureInfo.CurrentUICulture);
            }
    
            /// <summary>
            /// Overrides the standard DisplayName and looks up its value inside a resources file
            /// </summary>
            public override string DisplayName
            {
                get
                {
                    if (_displayName == null)
                    {
                        _displayName = GetResourceString(_displayNameResourceId);
                    }
                    return _displayName;
                }
            }
    
            /// <summary>
            /// Overrides the standard Description and looks up its value inside a resources file
            /// </summary>
            public override string Description
            {
                get
                {
                    if (_descriptionValue == null)
                    {
                        _descriptionValue = GetResourceString(_descriptionResourceId);
                    }
                    return _descriptionValue;
                }
            }
        }
    }
    

Ajouter un fichier de ressources et trois chaînes de ressources

Ensuite, ajoutez un fichier de ressources qui définit le nom de la règle, la description de la règle et la catégorie dans laquelle la règle apparaîtra dans l’interface de configuration de règle.

  1. Dans l’Explorateur de solutions, sélectionnez le projet SampleRules.

  2. Dans le menu Projet, sélectionnez Ajouter, puis Nouvel élément. La boîte de dialogue Ajouter un nouvel élément s’affiche.

  3. Dans la liste des Modèles installés, sélectionnez Général.

  4. Dans le volet Détails, sélectionnez Fichier de ressources.

  5. Dans Nom, tapez RuleResources.resx. L’éditeur de ressources s’affiche, sans aucune ressource définie.

  6. Définissez quatre chaînes de ressources comme suit :

    Nom Valeur
    AvoidWaitForDelay_ProblemDescription WAITFOR DELAY statement was found in {0}.
    AvoidWaitForDelay_RuleName Avoid using WaitFor Delay statements in stored procedures, functions and triggers.
    CategorySamples SamplesCategory
    CannotCreateResourceManager Can't create ResourceManager for {0} from {1}.
  7. Dans le menu Fichier, sélectionnez Enregistrer RuleResources.resx.

Définir la classe SampleConstants

Ensuite, définissez une classe qui fait référence aux ressources du fichier de ressources qui sont utilisées par Visual Studio pour afficher des informations à propos de votre règle dans l’interface utilisateur.

  1. Dans l’Explorateur de solutions, sélectionnez le projet SampleRules.

  2. Dans le menu Projet, sélectionnez Ajouter, puis Classe. La boîte de dialogue Ajouter un nouvel élément s’affiche.

  3. Dans la zone de texte Nom, tapez SampleRuleConstants.cs, puis sélectionnez le bouton Ajouter. Le fichier SampleRuleConstants.cs est ajouté au projet dans l’Explorateur de solutions.

  4. Ouvrez le fichier SampleRuleConstants.cs et ajoutez ce qui suit au fichier à l’aide d’instructions :

    namespace SampleRules
    {
        internal static class RuleConstants
        {
            /// <summary>
            /// The name of the resources file to use when looking up rule resources
            /// </summary>
            public const string ResourceBaseName = "Public.Dac.Samples.Rules.RuleResources";
    
            /// <summary>
            /// Lookup name inside the resources file for the select asterisk rule name
            /// </summary>
            public const string AvoidWaitForDelay_RuleName = "AvoidWaitForDelay_RuleName";
            /// <summary>
            /// Lookup ID inside the resources file for the select asterisk description
            /// </summary>
            public const string AvoidWaitForDelay_ProblemDescription = "AvoidWaitForDelay_ProblemDescription";
    
            /// <summary>
            /// The design category (should not be localized)
            /// </summary>
            public const string CategoryDesign = "Design";
    
            /// <summary>
            /// The performance category (should not be localized)
            /// </summary>
            public const string CategoryPerformance = "Design";
        }
    }
    
  5. Sélectionnez Fichier>Enregistrer.

Créer la classe de règle Code Analysis personnalisée

Après avoir ajouté les classes d’assistance qui seront utilisées par la règle d’analyse du code personnalisée, créez une classe de règle personnalisée et nommez-la AvoidWaitForDelayRule. La règle personnalisée AvoidWaitForDelayRule servira à éviter que les développeurs de base de données utilisent des instructions WAITFOR DELAY dans des procédures stockées, des déclencheurs et des fonctions.

Créer la classe AvoidWaitForDelayRule

  1. Dans l’Explorateur de solutions, sélectionnez le projet SampleRules.

  2. Dans le menu Projet, sélectionnez Ajouter, puis Classe. La boîte de dialogue Ajouter un nouvel élément s’affiche.

  3. Dans la zone de texte Nom, tapez AvoidWaitForDelayRule.cs, puis sélectionnez Ajouter. Le fichier AvoidWaitForDelayRule.cs est ajouté au projet dans l’Explorateur de solutions.

  4. Ouvrez le fichier AvoidWaitForDelayRule.cs et ajoutez ce qui suit au fichier à l’aide d’instructions :

    using Microsoft.SqlServer.Dac.CodeAnalysis;
    using Microsoft.SqlServer.Dac.Model;
    using Microsoft.SqlServer.TransactSql.ScriptDom;
    using System;
    using System.Collections.Generic;
    using System.Globalization;
    namespace SampleRules {
        class AvoidWaitForDelayRule {}
    }
    
  5. Dans la déclaration de classe AvoidWaitForDelayRule, rendez public le modificateur d’accès :

    /// <summary>
    /// This is a rule that returns a warning message
    /// whenever there is a WAITFOR DELAY statement appears inside a subroutine body.
    /// This rule only applies to stored procedures, functions and triggers.
    /// </summary>
    public sealed class AvoidWaitForDelayRule
    
  6. Faites dériver la classe AvoidWaitForDelayRule de la classe de base Microsoft.SqlServer.Dac.CodeAnalysis.SqlCodeAnalysisRule :

    public sealed class AvoidWaitForDelayRule : SqlCodeAnalysisRule
    
  7. Ajoutez LocalizedExportCodeAnalysisRuleAttribute à votre classe.

    LocalizedExportCodeAnalysisRuleAttribute permet au service d’analyse du code de découvrir les règles Code Analysis personnalisées. Seules les classes marquées avec un ExportCodeAnalysisRuleAttribute (ou un attribut qui en hérite) peuvent être utilisées dans l’analyse de code.

    LocalizedExportCodeAnalysisRuleAttribute fournit certaines métadonnées requises utilisées par le service, notamment un ID unique pour cette règle, un nom complet qui est affiché dans l’interface utilisateur Visual Studio et une Description qui peut être utilisée par votre règle lors de l’identification des problèmes.

    [LocalizedExportCodeAnalysisRule(AvoidWaitForDelayRule.RuleId,
        RuleConstants.ResourceBaseName,
        RuleConstants.AvoidWaitForDelay_RuleName,
        RuleConstants.AvoidWaitForDelay_ProblemDescription
        Category = RuleConstants.CategoryPerformance,
        RuleScope = SqlRuleScope.Element)]
    public sealed class AvoidWaitForDelayRule : SqlCodeAnalysisRule
    {
       /// <summary>
       /// The Rule ID should resemble a fully-qualified class name. In the Visual Studio UI
       /// rules are grouped by "Namespace + Category", and each rule is shown using "Short ID: DisplayName".
       /// For this rule, that means the grouping will be "Public.Dac.Samples.Performance", with the rule
       /// shown as "SR1004: Avoid using WaitFor Delay statements in stored procedures, functions and triggers."
       /// </summary>
       public const string RuleId = "RuleSamples.SR1004";
    }
    

    La propriété RuleScope doit être Microsoft.SqlServer.Dac.CodeAnalysis.SqlRuleScope.Element, car cette règle analyse des éléments spécifiques. La règle est appelée une fois pour chaque élément correspondant dans le modèle. Si vous souhaitez analyser un modèle entier, vous pouvez utiliser Microsoft.SqlServer.Dac.CodeAnalysis.SqlRuleScope.Model à la place.

  8. Ajoutez un constructeur qui configure les Microsoft.SqlServer.Dac.CodeAnalysis.SqlAnalysisRule.SupportedElementTypes. Il est nécessaire pour les règles de portée d’élément. Il définit les types d’élément auxquels cette règle s’applique. Dans notre exemple, la règle est appliquée aux procédures stockées, aux fonctions et aux déclencheurs. La classe Microsoft.SqlServer.Dac.Model.ModelSchema répertorie tous les types d’élément disponibles qui peuvent être analysés.

    public AvoidWaitForDelayRule()
    {
       // This rule supports Procedures, Functions and Triggers. Only those objects will be passed to the Analyze method
       SupportedElementTypes = new[]
       {
          // Note: can use the ModelSchema definitions, or access the TypeClass for any of these types
          ModelSchema.ExtendedProcedure,
          ModelSchema.Procedure,
          ModelSchema.TableValuedFunction,
          ModelSchema.ScalarFunction,
    
          ModelSchema.DatabaseDdlTrigger,
          ModelSchema.DmlTrigger,
          ModelSchema.ServerDdlTrigger
       };
    }
    
  9. Ajoutez un remplacement pour la méthode Microsoft.SqlServer.Dac.CodeAnalysis.SqlAnalysisRule.Analyze (Microsoft.SqlServer.Dac.CodeAnalysis.SqlRuleExecutionContext), qui utilise Microsoft.SqlServer.Dac.CodeAnalysis.SqlRuleExecutionContext comme paramètres d’entrée. Cette méthode retourne une liste de problèmes potentiels.

    La méthode obtient le Microsoft.SqlServer.Dac.Model.TSqlModel, Microsoft.SqlServer.Dac.Model.TSqlObject et TSqlFragment à partir du paramètre de contexte. La classe WaitForDelayVisitor est ensuite utilisée pour obtenir une liste de toutes les instructions WAITFOR DELAY dans le modèle.

    Pour chaque instruction WaitForStatement dans cette liste, un Microsoft.SqlServer.Dac.CodeAnalysis.SqlRuleProblem est créé.

    /// <summary>
    /// For element-scoped rules the Analyze method is executed once for every matching
    /// object in the model.
    /// </summary>
    /// <param name="ruleExecutionContext">The context object contains the TSqlObject being
    /// analyzed, a TSqlFragment
    /// that's the AST representation of the object, the current rule's descriptor, and a
    /// reference to the model being
    /// analyzed.
    /// </param>
    /// <returns>A list of problems should be returned. These will be displayed in the Visual
    /// Studio error list</returns>
    public override IList<SqlRuleProblem> Analyze(
        SqlRuleExecutionContext ruleExecutionContext)
    {
         IList<SqlRuleProblem> problems = new List<SqlRuleProblem>();
    
         TSqlObject modelElement = ruleExecutionContext.ModelElement;
    
         // this rule does not apply to inline table-valued function
         // we simply do not return any problem in that case.
         if (IsInlineTableValuedFunction(modelElement))
         {
             return problems;
         }
    
         string elementName = GetElementName(ruleExecutionContext, modelElement);
    
         // The rule execution context has all the objects we'll need, including the
         // fragment representing the object,
         // and a descriptor that lets us access rule metadata
         TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
         RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
    
         // To process the fragment and identify WAITFOR DELAY statements we will use a
         // visitor
         WaitForDelayVisitor visitor = new WaitForDelayVisitor();
         fragment.Accept(visitor);
         IList<WaitForStatement> waitforDelayStatements = visitor.WaitForDelayStatements;
    
         // Create problems for each WAITFOR DELAY statement found
         // When creating a rule problem, always include the TSqlObject being analyzed. This
         // is used to determine
         // the name of the source this problem was found in and a best guess as to the
         // line/column the problem was found at.
         //
         // In addition if you have a specific TSqlFragment that is related to the problem
         //also include this
         // since the most accurate source position information (start line and column) will
         // be read from the fragment
         foreach (WaitForStatement waitForStatement in waitforDelayStatements)
         {
            SqlRuleProblem problem = new SqlRuleProblem(
                String.Format(CultureInfo.CurrentCulture,
                    ruleDescriptor.DisplayDescription, elementName),
                modelElement,
                waitForStatement);
            problems.Add(problem);
        }
        return problems;
    }
    
    private static string GetElementName(
        SqlRuleExecutionContext ruleExecutionContext,
        TSqlObject modelElement)
    {
        // Get the element name using the built in DisplayServices. This provides a number of
        // useful formatting options to
        // make a name user-readable
        var displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
        string elementName = displayServices.GetElementName(
            modelElement, ElementNameStyle.EscapedFullyQualifiedName);
        return elementName;
    }
    
    private static bool IsInlineTableValuedFunction(TSqlObject modelElement)
    {
        return TableValuedFunction.TypeClass.Equals(modelElement.ObjectType)
                       && FunctionType.InlineTableValuedFunction ==
            modelElement.GetMetadata<FunctionType>(TableValuedFunction.FunctionType);
    }
    
  10. Sélectionnez Fichier>Enregistrer.

Générer la bibliothèque de classes

  1. Dans le menu Projet, sélectionnez Propriétés de SampleRules.

  2. Sélectionnez l’onglet Signature.

  3. Sélectionnez Signer l’assembly.

  4. Dans Choisir un fichier de clé de nom fort, sélectionnez <Nouveau>.

  5. Dans la zone de dialogue Créer une clé de nom fort, dans Nom du fichier de clé, tapez MyRefKey.

  6. (facultatif) Vous pouvez spécifier un mot de passe pour votre fichier de clé de nom fort.

  7. Cliquez sur OK.

  8. Dans le menu Fichier, sélectionnez Enregistrer tout.

  9. Dans le menu Générer, sélectionnez Générer la solution.

Installer une règle Code Analysis statique

Ensuite, vous devez installer l’assembly afin qu’il se charge lorsque vous générez et déployez des projets SQL Server.

Pour installer une règle, vous devez copier l’assembly et le fichier .pdb qui lui est associé dans le dossier Extensions.

Installer l’assembly SampleRules

Puis, vous copiez les informations d’assembly dans le répertoire Extensions. Lorsque Visual Studio démarre, il identifie toutes les extensions présentes dans l’annuaire <Visual Studio Install Dir>\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\Extensions et les sous-répertoires, et les rend utilisables.

Pour Visual Studio 2022, <Visual Studio Install Dir> est généralement C:\Program Files\Microsoft Visual Studio\2022\Enterprise. Remplacez Enterprise par Professional ou Community en fonction de votre édition Visual Studio installée.

Copiez le fichier d’assembly SampleRules.dll du répertoire de sortie vers le répertoire <Visual Studio Install Dir>\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\Extensions. Par défaut, le chemin d’accès de votre fichier .dll compilé est YourSolutionPath\YourProjectPath\bin\Debug ou YourSolutionPath\YourProjectPath\bin\Release.

Remarque

Vous devrez peut-être créer l’annuaire Extensions.

Votre règle doit maintenant être installée et apparaît une fois que vous aurez redémarré Visual Studio. Vous démarrez ensuite une nouvelle session de Visual Studio et vous créez un projet de base de données.

Démarrer une nouvelle session de Visual Studio et créer un projet de base de données

  1. Démarrez une deuxième session de Visual Studio.

  2. Sélectionnez Fichier>Nouveau>Projet.

  3. Dans la zone de dialogue Nouveau projet, recherchez et sélectionnez Projet de base de données SQL Server.

  4. Dans la zone de texte Nom, tapez SampleRulesDB et sélectionnez OK.

Enfin, la nouvelle règle s’affiche dans le projet SQL Server. Pour afficher la nouvelle règle Code Analysis AvoidWaitForRule :

  1. Dans l’Explorateur de solutions, sélectionnez le projet SampleRulesDB.

  2. Dans le menu Projet, sélectionnez Propriétés. La page Propriétés SampleRulesDB apparaît.

  3. Sélectionnez Code Analysis. Vous devez voir une nouvelle catégorie nommée RuleSamples.CategorySamples.

  4. Développez RuleSamples.CategorySamples. SR1004: Avoid WAITFOR DELAY statement in stored procedures, triggers, and functions doit s’afficher.