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

Article
08/23/2010

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 SQL static code analysis by using the following processes:

Create a class library, sign the project, and add the necessary references.
Create some 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 need to have either Visual Studio Team System Database Edition or Visual Studio Team System 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, under Project Types, click Visual C#.
Under Templates, select Class Library.
In the Name text box, type SampleRules and then click OK.

Next, you will sign the project.

To sign 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 Add Reference Dialog Box.
Select the .NET tab.
In the Component Name column, locate the following components:
Tip

Press CTRL while clicking to select multiple components.
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 Helper Classes

Before you create the class for the rule itself, you will add two helper classes to the project.

Tip

These helper classes might be useful for creating additional custom rules.

The first helper class is SqlRule.cs, which inherits from Rule and overrides both the constructor and some methods.

The SqlRule class will be inherited from the custom Code Analysis rule class that you will create later in this walkthrough, in the Creating the Custom Code Analysis Rule Class section.

To add the SqlRule.cs file to the project

In Solution Explorer, right-click the Class1.cs, click Rename, and type SqlRule.cs.

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Data.Schema.SchemaModel;
using Microsoft.Data.Schema.Sql.SchemaModel.SqlServer;
using Microsoft.Data.Schema.StaticCodeAnalysis;

namespace SampleRules
{

In the SqlRule class declaration, change the access modifier to internal abstract, because it is intended only to be a base class of other classes:
```
    internal abstract class SqlRule
    {
```

Derive the SqlRule class from Rule:

    internal abstract class SqlRule : Rule
    {

Add the constructor:

        /// <summary>
        /// Default constructor.
        /// </summary>
        public SqlRule(
            string idNamespace,
            string id,
            string name,
            string description,
            string helpUrl,
            string helpKeyword)
            : base(
                idNamespace,
                id,
                name,
                description,
                helpUrl,
                helpKeyword)
        {            
        }

Override the IsSupported method, which uses IModelElement as an input parameter:

        /// <summary>
        /// Specify whether a particular element is supported by the rule.
        /// </summary>
        /// <param name="element"></param>
        /// <returns></returns>
        public override bool IsSupported(IModelElement element)
        {
            // by default, we would like to handle every element.
            return true;
        }

Override the Analyze(IModelElement, RuleSetting) method, which uses IModelElement and RuleSetting as input parameters. The method then casts the IModelElement input parameter to SqlSchemaModel and ISqlModelElement SQL-specific types.

        /// <summary>
        /// Method to analyze a model element and return problems if any.
        /// </summary>
        /// <param name="modelElement"></param>
        /// <param name="ruleSetting"></param>
        /// <returns></returns>
        public override IList<Problem> Analyze(IModelElement modelElement, RuleSetting ruleSetting, RuleExecutionContext context)
        {
            // casting to SQL-specific types
            SqlSchemaModel sqlSchemaModel = modelElement.Model as SqlSchemaModel;
            ISqlModelElement sqlElement = modelElement as ISqlModelElement;

            return Analyze(sqlSchemaModel, sqlElement, ruleSetting, context);
        }

Add a protected abstract method for the Analyze method. For more information, see abstract (C# Reference).

        /// <summary>
        /// Abstract method to analyze sql model element.
        /// </summary>
        /// <returns>a list of problems</returns>
        protected abstract IList<Problem> Analyze(SqlSchemaModel sqlSchemaModel, ISqlModelElement sqlModelElement, RuleSetting ruleSetting, RuleExecutionContext context);
    }
}

From the File menu, click Save.

The second helper 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:

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 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 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.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Data.Schema.SchemaModel;
using Microsoft.Data.Schema.Sql.SchemaModel.SqlServer;
using System.Diagnostics;
using System.IO;
using Microsoft.Data.Schema.StaticCodeAnalysis;

namespace SampleRules
{

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

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

        /// <summary>
        /// Compute the start Line/Col and the end Line/Col to update problem information.
        /// </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(Problem 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");
                        }
                    }
                }
            }
        }

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 IScriptSourcedElement and then uses ElementSource 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;

            IScriptSourcedElement scriptSourcedElement = element as IScriptSourcedElement;
            if (scriptSourcedElement != null)
            {
                ElementSource elementSource = scriptSourcedElement.ElementSource;
                if (elementSource != null)
                {
                    fileName = elementSource.CacheIdentifier;
                }
            }

            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;
        }
    }
}

From 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 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.Linq;
using System.Text;
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.SqlServer;
using Microsoft.Data.Schema.Sql.SqlDsp;
using Microsoft.Data.Schema.StaticCodeAnalysis;

namespace SampleRules
{

In the AvoidWaitForDelayRule class declaration, change the access modifier to internal:
```
    internal class AvoidWaitForDelayRule
```
Derive the AvoidWaitForDelayRule class from the SqlRule helper class that you created previously:
```
    internal class AvoidWaitForDelayRule : SqlRule
```
Add the DatabaseSchemaProviderCompatibilityAttribute attribute. For more information, see Generate Specialized Test Data with a Custom Data Generator.
```
[DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
    internal class AvoidWaitForDelayRule : SqlRule
```

Create the member variables:

    {
        #region constants
        public const string SampleNamespace = "MyRules.SampleRules";
        public const string AvoidWaitForDelayRuleId = "SR1001";
        public const string AvoidWaitForDelayRuleName = @"Avoid WAITFOR DELAY statement in stored procedures, triggers and functions.";
        public const string AvoidWaitForDelayProblemDescription = @"WAITFOR DELAY statement found in {0}.";
        #endregion

Create the constructor:

        #region ctor
        /// <summary>
        /// The default constructor.
        /// </summary>
        public AvoidWaitForDelayRule()
            : base(
                SampleNamespace,
                AvoidWaitForDelayRuleId,
                AvoidWaitForDelayRuleName,
                AvoidWaitForDelayProblemDescription,
                String.Empty,
                String.Empty)
        {
        }
        #endregion

Add an override for IsSupported, which uses the input parameter IModelElement. The method uses ISqlProcedure, ISqlTrigger, ISqlFunction, and ISqlInlineTableValuedFunction in determining the Boolean output parameter.

        /// <summary>
        ///  Specifies this rule only checks Procedures/Functions/Triggers
        /// </summary>
        /// <param name="element"></param>
        /// <returns></returns>
        public override bool IsSupported(IModelElement element)
        {
            return
                element is ISqlProcedure ||
                element is ISqlTrigger ||
                (element is ISqlFunction && !(element is ISqlInlineTableValuedFunction));
        }

Add an override for Analyze(IModelElement, RuleSetting). The method uses SqlSchemaModel, ISqlModelElement, and RuleSetting as input parameters.

The method casts ISqlModelElement as IScriptSourcedElement to create an instance of TSqlFragment, which is used to create a ScriptDom for the model element.

If the TSqlFragment is not equal to null, the code uses a StatementList along with ProcedureStatementBodyBase and CreateTriggerStatement to specify that the rule only applies to triggers, procedures, and functions.

If the resulting StatementList is not equal to null, the method uses a List<T> of WaitForStatement, calls the FindWaitForDelay method (added later in this procedure) for each TSqlStatement in the StatementList, and creates a Problem for each WaitForStatement returned.

        /// <summary>
        /// Analyze the model element.
        /// </summary>
        protected override IList<Problem> Analyze(SqlSchemaModel sqlSchemaModel, ISqlModelElement sqlModelElement, RuleSetting ruleSetting, RuleExecutionContext context)
        {
            if (sqlSchemaModel == null)
            {
                throw new ArgumentException("SqlSchemaModel is expected", "dataSchemaModel");
            }
            if (sqlModelElement == null)
            {
                throw new ArgumentException("ISqlModelElement is expected", "modelElement");
            }

            List<Problem> problems = new List<Problem>();

            // Get ScriptDom for this model element
            TSqlFragment sqlFragment = null;
            IScriptSourcedElement scriptSourcedElement = sqlModelElement as IScriptSourcedElement;
            if (scriptSourcedElement != null && scriptSourcedElement.ElementSource != null)
            {
                sqlFragment = scriptSourcedElement.ElementSource.ScriptDom as TSqlFragment;
            }

            if (sqlFragment != null)
            {
                // extract statement list from the sql fragment
                StatementList statementList = null;
                if (sqlFragment is ProcedureStatementBodyBase)  // procs & functions
                {
                    statementList = (sqlFragment as ProcedureStatementBodyBase).StatementList;
                }
                else if (sqlFragment is CreateTriggerStatement)  // triggers
                {
                    statementList = (sqlFragment as CreateTriggerStatement).StatementList;
                }

                if (statementList != null)
                {
                    // in statement list, search all WaitFor statment
                    List<WaitForStatement> waitForDelayList = new List<WaitForStatement>();
                    foreach (TSqlStatement statement in statementList.Statements)
                    {
                        FindWaitForDelay(statement, waitForDelayList);
                    }

                    // Create problems for each WAITFOR DELAY statement found 
                    foreach (WaitForStatement waitForStatement in waitForDelayList)
                    {
                        Problem p = new Problem(this,
                                                string.Format(CultureInfo.CurrentCulture, this.Description, sqlModelElement.ToString()),
                                                sqlModelElement);
                        SqlRuleUtils.UpdateProblemPosition(p, waitForStatement.StartOffset, waitForStatement.FragmentLength);
                        problems.Add(p);
                    }
                }
            }
            return problems;
        }

Add a supporting method named FindWaitForDelay, which uses TSqlStatement and WaitForStatement as input parameters. The method searches for all occurrences of the WaitForStatement by using the WaitForOption set to Delay in a TSqlStatement. The method uses recursion to search for WaitForStatement occurrences within IfStatement, WhileStatement, BeginEndBlockStatement, and TryCatchStatement blocks.

        #region Supporting methods

        /// <summary>
        ///     Recursively find all WAITFOR DELAY statements in TSqlStatement
        /// </summary>
        /// <param name="st">statement input</param>
        /// <param name="waitForDelayList">WAITFOR DELAY statements found</param>
        private void FindWaitForDelay(TSqlStatement st, List<WaitForStatement> waitForDelayList)
        {
            if (st is WaitForStatement)
            {
                WaitForStatement waitForStatement = (WaitForStatement)st;
                if (waitForStatement.WaitForOption == WaitForOption.Delay)  // Only looking for WAITFOR DELAY occurrences
                {
                    waitForDelayList.Add(waitForStatement);
                }
            }
            else if (st is IfStatement)
            {
                IfStatement ifStatement = (IfStatement)st;
                FindWaitForDelay((ifStatement.ThenStatement) as TSqlStatement, waitForDelayList);
                FindWaitForDelay((ifStatement.ElseStatement) as TSqlStatement, waitForDelayList);
            }
            else if (st is WhileStatement)
            {
                WhileStatement whileStatement = (WhileStatement)st;
                FindWaitForDelay((whileStatement.Statement) as TSqlStatement, waitForDelayList);
            }
            else if (st is BeginEndBlockStatement)
            {
                BeginEndBlockStatement stBlock = (BeginEndBlockStatement)st;
                foreach (TSqlStatement s in stBlock.StatementList.Statements)
                {
                    FindWaitForDelay(s, waitForDelayList);
                }
            }
            else if (st is TryCatchStatement)
            {
                TryCatchStatement tryCatchStatement = (TryCatchStatement)st;
                foreach (TSqlStatement s in tryCatchStatement.TryStatements.Statements)
                {
                    FindWaitForDelay(s, waitForDelayList);
                }
                foreach (TSqlStatement s in tryCatchStatement.CatchStatements.Statements)
                {
                    FindWaitForDelay(s, waitForDelayList);
                }
            }
        }
        #endregion
    }
}

From 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, the path of your compiled .dll file is YourSolutionPath\bin\Debug or YourSolutionPath\bin\Release.
```
? System.Reflection.Assembly.LoadFrom(@"FilePath").FullName
```
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 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 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.AvoidWaitForDelayRule" assembly="SampleRules, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b4deb9b383d021b0" enabled="true"/>
</extensions>

From the File menu, click Save.

Next, you will copy the assembly information and the XML file to the Extensions directory. When Database Edition starts, it will identify any extensions in the <Microsoft Visual Studio 9.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 9.0>\VSTSDB\Extensions\ directory.
Copy the SampleRules.dll assembly file from the <Projects>\SampleRules\SampleRules\bin\Debug\ directory to the <Microsoft Visual Studio 9.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 9.0>\VSTSDB\Extensions\CustomRules directory that you created.

Tip

A best practice is to put your extension assemblies in a folder in the <Microsoft Visual Studio 9.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 SQL Server project.

To start a new Visual Studio session and create a SQL Server project

Start a second session of Database Edition.
On the File menu, click New and then click Project.
In the New Project dialog box, under Project Types, click Database Projects, and then click SQL Server 2008.
Under Templates, 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 CodeAnalysis.

You should see a new category named Microsoft.Samples.
Expand Microsoft.Samples.

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