Click or drag to resize
How To: Create Analysis Rule with Visual Studio

In this tutorial will step by step create and test an example new rule which to force the usage of NOLOCK hint.

Here is what we will do:

Create a new Analysis Template project

Before creating the analysis rule, first create a new analysis template which to extend the default SQL Enlight template.

  1. Start with creating the template as described in the tutorial How To: Extend Analysis Template with Visual Studio.

  2. Create a new Batch Analysis Rule named RequireNolock.

  3. Create a new test script file RequireNolock.sql.

Set rule properties

Modify the InitializeRule method of the RequireNolock class like this:

C#
 1/// <summary>
 2/// Initialize rule base properties and add rule parameters.
 3/// </summary>
 4private void InitializeRule()
 5{
 6    this.Name = this.GetType().Name; //"Rule1"
 7    this.Message = "The NOLOCK table hint is not set for the table.";
 8    this.Description = "This is an example analysis rule which checks for table references inside SELECT statements, that does not have NOLOCK table hint.";
 9    this.Author = "Ubitsoft";
10    this.Scope = AnalysisRuleScope.Batch;
11    this.Type = AnalysisRuleType.XsltExpression;
12    this.SeverityLevel = AnalysisRuleSeverityLevel.Warning;
13
14
15    // IMPORTANT: Make sure the rule expression .xslt file has "Build Action" property set to "Embedded Resource".
16    this.RuleExpression = GetRuleExpression();
17
18    // IMPORTANT: Make sure the rule test script .sql file has "Build Action" property set to "Embedded Resource".
19    this.TestScript = GetRuleTestScript();
20
21    AnalysisRuleParameter ruleSuppressMarkParameter = new AnalysisRuleParameter();
22    ruleSuppressMarkParameter.Name = "RuleSuppressMark";
23    ruleSuppressMarkParameter.Description = "Comment text which to indicate that the table source should be ignored during rule evaluation.";
24    ruleSuppressMarkParameter.DefaultValue = string.Concat("IGNORE:", this.Name);
25
26    this.Parameters = new Dictionary<string, AnalysisRuleParameter>();
27    this.Parameters.Add(ruleSuppressMarkParameter.Name, ruleSuppressMarkParameter);
28
29    this.RequireRuleParameters = false;
30
31    // Additional information and references.
32    this.AdditionalInfos = new AdditionalInfo[1];
33    this.AdditionalInfos[0] = new AdditionalInfo("Table Hints (Transact-SQL)", "http://msdn.microsoft.com/en-us/library/ms187373.aspx");
34
35}
Edit rule expression

Open the RequireNolock.xslt and replace its content with the following XSLT code:

 1<xsl:rule xmlns:xsl="http://schemas.ubitsoft.com/analysis-rule-expression" xmlns="http://schemas.ubitsoft.com/analysis-rule-expression">
 2  <!--
 3    Name:        RequireNolock
 4    Description: The rule checks for not specified NOLOCK table hint.
 5    Author:       Ubitsoft
 6  -->
 7
 8  <!-- Get rule parameters. -->
 9  <xsl:variable name="RuleSuppressMark" select="$parameters/Param[@Name='RuleSuppressMark']/text()"/>
10
11  <!-- Select all FROM clauses in the batch. -->
12  <xsl:for-each select="$batch//k:from[parent::k:select]">
13    <xsl:variable name="from-clause" select="."/>
14
15    <!-- 
16    Generate unique id which to be used for restricting the scope of the FROM clause and identifying 
17    the parent FROM clause of given table source. 
18    -->
19    <xsl:variable name="from-clause-id" select="generate-id($from-clause)"/>
20
21    <!-- Select table source container elements. -->
22    <xsl:variable name="table-source-containers" select="$from-clause/g:commalist 
23                                                      |  $from-clause//k:join"/>
24
25    <!-- 
26    Select and iterate the single or multipart identifiers which are direct children of the table source containers.
27    -->
28    <xsl:for-each select="$table-source-containers/*[self::pu:dot or self::i:* or self::k:as/*[not(self::cmt:*)][1]/self::*[self::i:* or self::pu:dot]]">
29      <xsl:variable name="table-source" select="."/>
30
31      <!-- If the table is aliased, the hints are under the AS keyword element, otherwise the hints are under the identifier element. -->
32      <xsl:variable name="table-hint-target" select="$table-source[not(self::pu:dot)]
33                                                 | $table-source/self::pu:dot/descendant-or-self::pu:dot[not(pu:dot)][1]/i:*[last()]"/>
34
35      <!-- Check for existence each possible way of setting a table hint. -->
36      <xsl:if test="$table-hint-target[not($table-hint-target/g:brackets/g:commalist/i:common[str2:compare(@name,'nolock',true())=0] or 
37                    $table-hint-target/g:brackets/g:commalist/g:expression/i:common[str2:compare(@name,'nolock',true())=0] or             
38                    $table-hint-target/g:brackets/g:commalist/pr:hint[@name='nolock'] or
39                    $table-hint-target/k:with/g:brackets/g:commalist/pr:hint[@name='nolock'] )]">
40
41        <xsl:variable name="table-identifier" select="$table-hint-target/self::k:as/*[not(self::cmt:*)][1]/self::*[self::i:* or self::pu:dot]
42                          | $table-hint-target/self::i:*"/>
43
44        <!-- The hint was not matched and we return rule violation information by calling the "output-message" template. -->
45        <xsl:call-template name="output-message">
46          <xsl:with-param name="line" select="$table-hint-target/@se:sline"/>
47          <xsl:with-param name="column" select="$table-hint-target/@se:scol"/>
48          <xsl:with-param name="msg" select="$v-rulename"/>
49          <xsl:with-param name="desc" select="concat($v-rulename,' : The NOLOCK table hint is not set for table [', $table-identifier/@name,'].')"/>
50          <xsl:with-param name="near" select="$table-identifier/@name"/>
51          <xsl:with-param name="type" select="$v-ruleseverity"/>
52        </xsl:call-template>
53      </xsl:if>
54    </xsl:for-each>
55
56  </xsl:for-each>
57
58</xsl:rule>
Test and debug the rule expression
Note Note

Visual Studio edition which supports XSLT debugging is required in order to debug the analysis rule expression.

  1. Add a new Console Application project named MyCompany.SqlEnlight.Analysis.TestApp to the solution.

  2. Add the following references to the new project:

    • MyCompany.SqlEnlight.Analysis.Rules project

    • Ubitsoft.SqlEnlight.Model assembly

    • Ubitsoft.SqlEnlight.Sdk assembly

  3. Replace the content of the Program.cs with the following code:

    C#
     1using System;
     2using System;
     3using System.Collections.Generic;
     4using System.Configuration;
     5using System.Data.SqlClient;
     6using System.IO;
     7using System.Text;
     8using System.Xml.XPath;
     9using System.Xml.Xsl;
    10using Ubitsoft.SqlEnlight.Model;
    11using Ubitsoft.SqlEnlight.Model.Analysis;
    12using Ubitsoft.SqlEnlight.Sdk;
    13
    14namespace MyCompany.SqlEnlight.Analysis.TestApp
    15{
    16    class Program
    17    {
    18        static void Main(string[] args)
    19        {
    20
    21            Ubitsoft.SqlEnlight.Sdk.ISdkServiceProvider sdk = new SdkServiceProvider();
    22
    23            ISdkAnalysisService analysisService = sdk.Analysis;
    24
    25            AnalysisRule rule = new MyCompany.SqlEnlight.Analysis.Rules.RequireNolock();
    26
    27            Error[] errors = analysisService.Debug(rule, 
    28                                                   rule.TestScript, 
    29                                                   rule.GetParametersDefaultValues(),
    30                                                   (XPathNavigator)null, 
    31                                                   TransformationOverrideHandler);
    32
    33            for (int i = 0; i < errors.Length; i++ )
    34            {
    35                Console.WriteLine("{0}: {1}", i+1, (errors[i].ToString()));
    36            }
    37        }
    38
    39        static void TransformationOverrideHandler(XslCompiledTransform transform, System.Xml.Xsl.XsltArgumentList xsltArgs, 
    40                                                  System.Xml.XmlReader reader, System.Xml.XmlWriter writer)
    41        {
    42            if (transform != null)
    43            {
    44                // Uncomment the line below in order for the debugger to break just before the transformation,
    45                // then step into the Transform method call (use Main Menu->Debug->Step Into command or just press F11).
    46                // System.Diagnostics.Debugger.Break();
    47
    48                transform.Transform(reader, xsltArgs, writer);
    49            }
    50            else
    51            {
    52                throw new NullReferenceException("XSLT Transformer object not initialized.");
    53            }
    54        }
    55    }
    56}

To debug the rule add breakpoint to the TransformationOverrideHandler method or uncomment the call to DebuggerBreak method.

tutorial-1-6
 

Stepping into the transformation will open the XSLT Editor and the XSLT Debugger will break on the beginning of a XSL stylesheet which contains the analysis rule expression.

Search for this string <xsl:template name="CART_requirenolock"> to locate the rule expression template. After locating the rule template, you can set a breakpoint inside the rule template and debug it the template using the XSLT Debugger.

tutorial-1-5
 

See Also