Walkthrough: Authoring a Custom Static Code Analysis Rule Assembly for SQL

Article
02/04/2013

This step-by-step topic demonstrates the steps used to create a SQL Code Analysis rule. The rule created in this walkthrough is used to avoid WAITFOR DELAY statements in stored procedures, triggers, and functions.

In this walkthrough, you will create a custom rule for Transact-SQL static code analysis by using the following processes:

Create a class library, enable signing for that project, and add the necessary references.
Create two helper C# classes.
Create a C# custom rule class.
Create an XML file that is used to register the assembly.
Copy the resulting DLL and XML file that you create into the Extensions directory in order to register it.
Verify that the new code analysis rule is in place.

Prerequisites

You must have either Visual Studio Premium or Visual Studio Ultimate installed to complete this walkthrough.

Creating a Custom Code Analysis Rule for SQL

First, you will create a class library.

To create a class library

On the File menu, click New and then click Project.
In the New Project dialog box, in the list of Installed Templates, click Visual C#.
In the details pane, select Class Library.
In the Name text box, type SampleRules and then click OK.

Next, you will sign the project.

To enable signing for the project

With the SampleRules project node selected in Solution Explorer, from the Project menu, click Properties (or right-click the project node in Solution Explorer and then click Properties).
Click the Signing tab.
Select the Sign the assembly check box.
Specify a new key file. In the Choose a strong name key file drop-down list, select <New...>.

The Create Strong Name Key dialog box appears. For more information, see Create Strong Name Key Dialog Box.
In the Create Strong Name Key dialog box, type SampleRulesKey in the Name text box for the new key file. You do not have to provide a password for this walkthrough. For more information, see Managing Assembly and Manifest Signing.

Next, you will add the necessary references to the project.

To add applicable references to the project

In Solution Explorer, select the SampleRules project.
On the Project menu, click Add Reference.

The Add Reference dialog box opens. For more information, see How to: Add or Remove References in Visual Studio.
Select the .NET tab.
In the Component Name column, locate the following components:
Note

To select multiple components, press and hold CTRL while click each one.
Click OK when you have selected all the components you need.

The selected references will appear under the References node of the project in Solution Explorer.

Creating the Custom Code Analysis Rule Supporting Classes

Before you create the class for the rule itself, you will add a visitor class and a helper class to the project.

Tip

These classes might be useful for creating additional custom rules.

The first class that you must define is the WaitForDelayVisitor class, derived from TSqlConcreteFragmentVisitor . This class provides access to the WAITFOR DELAY statements in the model.

To define the WaitForDelayVisitor class

In Solution Explorer, select the SampleRules project.
On the Project menu, select Add Class.

The Add New Item dialog box appears.
In the Name text box, type WaitForDelayVisitor.cs and then click the Add button.

The WaitForDelayVisitor.cs file is added to the project in Solution Explorer.

Open the WaitForDelayVisitor.cs file and update the contents to match the following code:

using System.Collections.Generic;
using Microsoft.Data.Schema.ScriptDom.Sql;

namespace SampleRules
{
    class WaitForDelayVistor
    {
    }
}

In the class declaration, change the access modifier to internal and derive the class from TSqlConcreteFragmentVisitor:
```
    internal class WaitForDelayVisitor : TSqlConcreteFragmentVisitor
    {
    }
```

Add the following code to define the List member variable:

        private List<WaitForStatement> _waitForDelayStatments;

Define the class constructor by adding the following code:

        #region ctor
        public WaitForDelayVisitor()
        {
            _waitForDelayStatments = new List<WaitForStatement>();
        }
        #endregion

Define the read-only WaitForDelayStatements property by adding the following code:

        #region properties
        public List<WaitForStatement> WaitForDelayStatements
        {
            get
            {
                return _waitForDelayStatments;
            }
        }
        #endregion

Override the ExplicitVisit method by adding the following code:

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

This method visits the WAITFOR statements in the model, and adds those that have the DELAY option specified to the list of WAITFOR DELAY statements. The key class referenced here is WaitForStatement.

On the File menu, click Save.

The second class is SqlRuleUtils.cs, which contains some utility methods that will be used by the custom Code Analysis rule class that you will create later in this walkthrough, in the Creating the Custom Code Analysis Rule Class section. These methods include the following:

GetElementName Used to get the escaped, fully qualified name of a model element
UpdateProblemPosition Used to compute line and column information.
ReadFileContent Used to read content from a file.
GetElementSourceFile Used to acquire the source file.
ComputeLineColumn Used to convert the offset from ScriptDom to line and column in script files.

To add the SqlRuleUtils.cs file to the project

In Solution Explorer, select the SampleRules project.
On the Project menu, select Add Class.

The Add New Item dialog box appears.
In the Name text box, type SqlRuleUtils.cs and then click the Add button.

The SqlRuleUtils.cs file is added to the project in Solution Explorer.

Open the SqlRuleUtils.cs file and add the following using statements to the file:

using System;
using System.Diagnostics;
using System.IO;
using Microsoft.Data.Schema.SchemaModel;
using Microsoft.Data.Schema.Sql.SchemaModel;
using Microsoft.Data.Schema.StaticCodeAnalysis;
using Microsoft.Data.Schema;


namespace SampleRules
{
}

In the SqlRuleUtils class declaration, change the access modifier to public static:
```
    public static class SqlRuleUtils
    {
    }
```

Add the following code to create the GetElementName method, which uses SqlSchemaModel and ISqlModelElement as input parameters:

        /// <summary>
        /// Get escaped fully qualified name of a model element 
        /// </summary>
        /// <param name="sm">schema model</param>
        /// <param name="element">model element</param>
        /// <returns>name of the element</returns>
        public static string GetElementName(SqlSchemaModel sm, ISqlModelElement element)
        {
            return sm.DatabaseSchemaProvider.UserInteractionServices.GetElementName(element, ElementNameStyle.EscapedFullyQualifiedName);
        }

Add the following code to create the ReadFileContent method:

        /// <summary>
        /// Read file content from a file.
        /// </summary>
        /// <param name="filePath"> file path </param>
        /// <returns> file content in a string </returns>
        public static string ReadFileContent(string filePath)
        {
            //  Verify that the file exists first.
            if (!File.Exists(filePath))
            {
                Debug.WriteLine(string.Format("Cannot find the file: '{0}'", filePath));
                return string.Empty;
            }

            string content;
            using (StreamReader reader = new StreamReader(filePath))
            {
                content = reader.ReadToEnd();
                reader.Close();
            }
            return content;
        }

Add the following code to create the GetElementSourceFile method, which uses IModelElement as an input parameter and String to retrieve the file name. The method casts the IModelElement as IScriptSourcedModelElement and then uses ISourceInformation in determining the script file path from the model element.

        /// <summary>
        /// Get the corresponding script file path from a model element.
        /// </summary>
        /// <param name="element">model element</param>
        /// <param name="fileName">file path of the scripts corresponding to the model element</param>
        /// <returns></returns>
        private static Boolean GetElementSourceFile(IModelElement element, out String fileName)
        {
            fileName = null;

            IScriptSourcedModelElement scriptSourcedElement = element as IScriptSourcedModelElement;
            if (scriptSourcedElement != null)
            {
                ISourceInformation elementSource = scriptSourcedElement.PrimarySource;
                if (elementSource != null)
                {
                    fileName = elementSource.SourceName;
                }
            }

            return String.IsNullOrEmpty(fileName) == false;
        }

Add the following code to create the ComputeLineColumn method:

        /// This method converts offset from ScriptDom to line\column in script files.
        /// A line is defined as a sequence of characters followed by a carriage return ("\r"), 
        /// a line feed ("\n"), or a carriage return immediately followed by a line feed. 
        public static bool ComputeLineColumn(string text, Int32 offset, Int32 length,
                                            out Int32 startLine, out Int32 startColumn, out Int32 endLine, out Int32 endColumn)
        {
            const char LF = '\n';
            const char CR = '\r';

            // Setting the initial value of line and column to 0 since VS auto-increments by 1.
            startLine = 0;
            startColumn = 0;
            endLine = 0;
            endColumn = 0;

            int textLength = text.Length;

            if (offset < 0 || length < 0 || offset + length > textLength)
            {
                return false;
            }

            for (int charIndex = 0; charIndex < length + offset; ++charIndex)
            {
                char currentChar = text[charIndex];
                Boolean afterOffset = charIndex >= offset;
                if (currentChar == LF)
                {
                    ++endLine;
                    endColumn = 0;
                    if (afterOffset == false)
                    {
                        ++startLine;
                        startColumn = 0;
                    }
                }
                else if (currentChar == CR)
                {
                    // CR/LF combination, consuming LF.
                    if ((charIndex + 1 < textLength) && (text[charIndex + 1] == LF))
                    {
                        ++charIndex;
                    }

                    ++endLine;
                    endColumn = 0;
                    if (afterOffset == false)
                    {
                        ++startLine;
                        startColumn = 0;
                    }
                }
                else
                {
                    ++endColumn;
                    if (afterOffset == false)
                    {
                        ++startColumn;
                    }
                }
            }

            return true;
        }

Add the following code to create the UpdateProblemPosition method, which uses DataRuleProblem as an input parameter:

        /// <summary>
        /// Compute the start Line/Col and the end Line/Col to update problem info
        /// </summary>
        /// <param name="problem">problem found</param>
        /// <param name="offset">offset of the fragment having problem</param>
        /// <param name="length">length of the fragment having problem</param>
        public static void UpdateProblemPosition(DataRuleProblem problem, int offset, int length)
        {
            if (problem.ModelElement != null)
            {
                String fileName = null;
                int startLine = 0;
                int startColumn = 0;
                int endLine = 0;
                int endColumn = 0;

                bool ret = GetElementSourceFile(problem.ModelElement, out fileName);
                if (ret)
                {
                    string fullScript = ReadFileContent(fileName);

                    if (fullScript != null)
                    {
                        if (ComputeLineColumn(fullScript, offset, length, out startLine, out startColumn, out endLine, out endColumn))
                        {
                            problem.FileName = fileName;
                            problem.StartLine = startLine + 1;
                            problem.StartColumn = startColumn + 1;
                            problem.EndLine = endLine + 1;
                            problem.EndColumn = endColumn + 1;
                        }
                        else
                        {
                            Debug.WriteLine("Could not compute line and column");
                        }
                    }
                }
            }
        }

On the File menu, click Save.

Next, you add a resource file that will define the rule name, rule description, and the category in which the rule will appear in the rule configuration interface.

To add a resource file and three resource strings

In Solution Explorer, select the SampleRules project.
On the Project menu, select Add New Item.

The Add New Item dialog box appears.
In the list of Installed Templates, click General.
In the details pane, click Resources File.
In Name, type SampleRuleResource.resx.

The resource editor appears, with no resources yet defined.

Define three resource strings as follows:

Name	Value
AvoidWaitForDelay_ProblemDescription	WAITFOR DELAY statement was found in {0}.
AvoidWaitForDelay_RuleName	Avoid using WaitFor Delay statements in stored procedures, functions and triggers.
CategorySamples	SamplesCategory

On the File menu, click Save SampleRuleResource.resx.

Next, you define a class that references the resources in the resource file that are used by Visual Studio to display information about your rule in the user interface.

To define the SampleConstants class

In Solution Explorer, select the SampleRules project.
On the Project menu, select Add Class.

The Add New Item dialog box appears.
In the Name text box, type SampleRuleConstants.cs and then click the Add button.

The SampleRuleConstants.cs file is added to the project in Solution Explorer.

Open the SampleRuleConstants.cs file and add the following using statements to the file:

namespace SampleRules
{
    internal class SampleConstants
    {
        public const string NameSpace = "SamplesRules";
        public const string ResourceBaseName = "SampleRules.SampleRuleResource";
        public const string CategorySamples = "CategorySamples";

        public const string AvoidWaitForDelayRuleId = "SR1004";
        public const string AvoidWaitForDelay_RuleName = "AvoidWaitForDelay_RuleName";
        public const string AvoidWaitForDelay_ProblemDescription = "AvoidWaitForDelay_ProblemDescription";
    }
}

On the File menu, click Save.

Creating the Custom Code Analysis Rule Class

Now that you have added the helper classes that the custom Code Analysis rule will use, you will create a custom rule class and name it AvoidWaitForDelayRule. The AvoidWaitForDelayRule custom rule will be used to help database developers avoid WAITFOR DELAY statements in stored procedures, triggers, and functions.

To create the AvoidWaitForDelayRule class

In Solution Explorer, select the SampleRules project.
On the Project menu, select New Folder.
A new folder appears in Solution Explorer. Name the folder AvoidWaitForDelayRule.
In Solution Explorer, verify that the AvoidWaitForDelayRule folder is selected.
On the Project menu, select Add Class.

The Add New Item dialog box appears.
In the Name text box, type AvoidWaitForDelayRule.cs and then click the Add button.

The AvoidWaitForDelayRule.cs file is added to the project's AvoidWaitForDelayRule folder in Solution Explorer.

Open the AvoidWaitForDelayRule.cs file and add the following using statements to the file:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema.SchemaModel;
using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.Sql.SchemaModel;
using Microsoft.Data.Schema.Sql;
using Microsoft.Data.Schema.StaticCodeAnalysis;
namespace SampleRules
{
    public class AvoidWaitForDelayRule
    {
    }
}

Note

You must change the namespace name from SampleRules.AvoidWaitForDelayRule to SampleRules.

In the AvoidWaitForDelayRule class declaration, change the access modifier to public:

    /// <summary>
    /// This is a SQL rule which returns a warning message 
    /// whenever there is a WAITFOR DELAY statement appears inside a subroutine body. 
    /// This rule only applies to SQL stored procedures, functions and triggers.
    /// </summary>
    public class AvoidWaitForDelayRule

Derive the AvoidWaitForDelayRule class from the StaticCodeAnalysisRule base class:
```
    public class AvoidWaitForDelayRule : StaticCodeAnalysisRule
```

Add the DatabaseSchemaProviderCompatibilityAttribute, DataRuleAttribute, and SupportedElementTypeAttribute to your class. For more information about feature extension compatibility, see Extending the Database Features of Visual Studio.

    [DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
    [DataRuleAttribute(
        SampleConstants.NameSpace,
        SampleConstants.AvoidWaitForDelayRuleId,
        SampleConstants.ResourceBaseName,
        SampleConstants.AvoidWaitForDelay_RuleName,
        SampleConstants.CategorySamples,
        DescriptionResourceId = SampleConstants.AvoidWaitForDelay_ProblemDescription)]
    [SupportedElementType(typeof(ISqlProcedure))]
    [SupportedElementType(typeof(ISqlTrigger))]
    [SupportedElementType(typeof(ISqlFunction))]
    public class AvoidWaitForDelayRule : StaticCodeAnalysisRule

The DataRuleAttribute specifies information that appears in Visual Studio when you configure database code analysis rules. The SupportedElementTypeAttribute defines the types of elements to which this rule will be applied. In this case, the rule will be applied to stored procedures, triggers, and functions.

Add an override for the Analyze method, which uses DataRuleSetting and DataRuleExecutionContext as input parameters. This method returns a list of potential problems.

The method obtains the IModelElement and TSqlFragment from the context parameter. The SqlSchemaModel and ISqlModelElement are obtained from the model element. The WaitForDelayVisitor class is then used to obtain a list of all WAITFOR DELAY statements in the model.

For each WaitForStatement in that list, a DataRuleProblem is created.

        #region Overrides
        /// <summary>
        /// Analyze the model element
        /// </summary>
        public override IList<DataRuleProblem> Analyze(DataRuleSetting ruleSetting, DataRuleExecutionContext context)
        {
            List<DataRuleProblem> problems = new List<DataRuleProblem>();

            IModelElement modelElement = context.ModelElement;

            // this rule does not apply to inline table-valued function
            // we simply do not return any problem
            if (modelElement is ISqlInlineTableValuedFunction)
            {
                return problems;
            }

            // casting to SQL specific 
            SqlSchemaModel sqlSchemaModel = modelElement.Model as SqlSchemaModel;
            Debug.Assert(sqlSchemaModel!=null, "SqlSchemaModel is expected");

            ISqlModelElement sqlElement = modelElement as ISqlModelElement;
            Debug.Assert(sqlElement != null, "ISqlModelElement is expected");

            // Get ScriptDom for this model element
            TSqlFragment sqlFragment = context.ScriptFragment as TSqlFragment;
            Debug.Assert(sqlFragment != null, "TSqlFragment is expected");

            // visitor to get the ocurrences of WAITFOR DELAY statements
            WaitForDelayVisitor visitor = new WaitForDelayVisitor();
            sqlFragment.Accept(visitor);
            List<WaitForStatement> waitforDelayStatements = visitor.WaitForDelayStatements;

            // Create problems for each WAITFOR DELAY statement found 
            foreach (WaitForStatement waitForStatement in waitforDelayStatements)
            {
                DataRuleProblem problem = new DataRuleProblem(this,
                                            String.Format(CultureInfo.CurrentCulture, this.RuleProperties.Description, SqlRuleUtils.GetElementName(sqlSchemaModel, sqlElement)),
                                            sqlElement);

                SqlRuleUtils.UpdateProblemPosition(problem, waitForStatement.StartOffset, waitForStatement.FragmentLength);
                problems.Add(problem);
            }

            return problems;
        }

        #endregion

On the File menu, click Save.

Next, you will build the project.

To build the project

From the Build menu, click Build Solution.

Next, you will gather assembly information generated in the project, including the version, culture, and PublicKeyToken.

To gather assembly information

On the View menu, click Other Windows, and then click Command Window to open the Command window.
In the Command window, type the following code. For FilePath, substitute the path and file name of your compiled .dll file. Include the quotation marks around the path and file name.

Note

By default, FilePath is Projects\SampleRules\SampleRules\bin\Debug\YourDLL or Projects\SampleRules\SampleRules\bin\Release\YourDLL.
```
? System.Reflection.Assembly.LoadFrom(@"FilePath")
```
Press ENTER. The line should resemble the following with your specific PublicKeyToken:
```
"SampleRules, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn"
```
Notate or copy this assembly information; it will be used in the next procedure.

Next, you will create an XML file by using the assembly information that you gathered in the previous procedure.

To create the XML file

In Solution Explorer, select the SampleRules project.
On the Project menu, select Add New Item.
In the Templates pane, locate and select the XML File item.
In the Name text box, type SampleRules.Extensions.xml and then click the Add button.

The SampleRules.Extensions.xml file is added to the project in Solution Explorer.

Open the SampleRules.Extensions.xml file and update it to match the following XML. Replace the version, culture, and PublicKeyToken values with those that you retrieved in the previous procedure.

<?xml version="1.0" encoding="utf-8"?>
<extensions assembly=""
            version="1" xmlns="urn:Microsoft.Data.Schema.Extensions"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd">

  <extension type="SampleRules.AvoidWaitForDelayRule" assembly="SampleRules, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b4deb9b383d021b0" enabled="true"/>
</extensions>

On the File menu, click Save.

Next, you will copy the assembly information and the XML file to the Extensions directory. When Visual Studio starts, it will identify any extensions in the Microsoft Visual Studio 10.0\VSTSDB\Extensions directory and subdirectories, and register them for use in the session.

To copy the assembly information and XML file to the Extensions directory

Create a new folder named CustomRules in the Microsoft Visual Studio 10.0\VSTSDB\Extensions\ directory.
Copy the SampleRules.dll assembly file from the Projects\SampleRules\SampleRules\bin\Debug\ directory to the Microsoft Visual Studio 10.0\VSTSDB\Extensions\CustomRules directory that you created.
Copy the SampleRules.Extensions.xml file from the Projects\SampleRules\SampleRules\ directory to the Microsoft Visual Studio 10.0\VSTSDB\Extensions\CustomRules directory that you created.

Note

A best practice is to put your extension assemblies in a folder in the Microsoft Visual Studio 10.0\VSTSDB\Extensions directory. This will help you identify which extensions were included with the product, and which ones are your custom creations. Folders are also recommended for organizing your extensions into specific categories.

Next, you will start a new session of Visual Studio and create a database project.

To start a new Visual Studio session and create a database project

Start a second session of Visual Studio.
On the File menu, click New and then click Project.
In the New Project dialog box, in the list of Installed Templates, expand the Database Projects node, and then click SQL Server.
In the details pane, select SQL Server 2008 Database Project.
In the Name text box, type SampleRulesDB and then click OK.

Finally, you will see the new rule displaying in the SQL Server project.

To view the new AvoidWaitForRule Code Analysis rule

In Solution Explorer, select the SampleRulesDB project.
On the Project menu, click Properties.

The SampleRulesDB properties page is displayed.
Click Code Analysis.

You should see a new category named CategorySamples.
Expand CategorySamples.

You should see SR1004: Avoid WAITFOR DELAY statement in stored procedures, triggers, and functions.