Creating a Custom SSDT Test Condition

I was teaching our new SSDT2019 class today and, while discussing SQL Server Unit Testing, I was asked why the default Row Count test condition only tested for equality (and not less than, greater than, or inequality). I decided to build one that did just those things.

First, I dusted off the old guidance from (cough) 2012 and was amazed that the guidance still mostly applied. Using Visual Studio 2019, I created a new C# Class Library (.NET Framework) project named CustomTestConditions. I generated and associated a strong name key and then referenced System.ComponentModel.Composition and the Microsoft.Data.Tools.Schema.Sql.dll assembly.

Next, using the ResultSetColumnCountCondition sample code as a base (I’ve included that one too), I created the RowCount (Advanced) test condition class with the following code:

using System;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using Microsoft.Data.Tools.Schema.Sql.UnitTesting;
using Microsoft.Data.Tools.Schema.Sql.UnitTesting.Conditions;

namespace RowCountAdvancedCountCondition
{
  [System.ComponentModel.DesignerCategory("Code")]
  [ExportTestCondition("Row Count (Advanced)", typeof(RowCountAdvancedCountCondition))]
  public class RowCountAdvancedCountCondition : TestCondition
  {
    private int _resultSet;
    private string _comparison;
    private int _count;
    private int _batch;

    public RowCountAdvancedCountCondition()
    {
      _resultSet = 1;
      _comparison = "=";
      _count = 0;
      _batch = 1;
    }

    public override void Assert(DbConnection validationConnection, SqlExecutionResult[] results)
    {
      base.Assert(validationConnection, results);
      if (results.Length < _batch)
        throw new DataException(String.Format("Batch {0} does not exist", _batch));

      SqlExecutionResult result = results[_batch - 1];
      if (result.DataSet.Tables.Count < ResultSet)
        throw new DataException(String.Format("ResultSet {0} does not exist", ResultSet));

      DataTable table = result.DataSet.Tables[ResultSet - 1];

      if (_comparison == "<" && table.Rows.Count >= _count)
        throw new DataException(String.Format(
            "ResultSet {0}: {1} rows was not less than the {2} columns expected",
            ResultSet, table.Rows.Count, _count));
      if (_comparison == ">" && table.Rows.Count <= _count)
        throw new DataException(String.Format(
            "ResultSet {0}: {1} rows was not greater than the {2} columns expected",
            ResultSet, table.Rows.Count, _count));
      if (_comparison == "=" && table.Rows.Count != _count)
        throw new DataException(String.Format(
            "ResultSet {0}: {1} rows was not equal to the {2} columns expected",
            ResultSet, table.Rows.Count, _count));
      if (_comparison == "!" && table.Rows.Count == _count)
        throw new DataException(String.Format(
            "ResultSet {0}: {1} rows was equal to the {2} columns expected",
            ResultSet, table.Rows.Count, _count));
    }

    public override string ToString()
    {
      return String.Format(
          "Condition fails if ResultSet {0}'s row count is not {1} {2} rows",
          ResultSet, Comparison, Count);
    }

    #region Properties  

    [Category("Test Condition")]
    [DisplayName("ResultSet")]
    [Description("ResultSet Number")]
    public int ResultSet
    {
      get { return _resultSet; }
      set
      {
        if (value < 1)
          throw new ArgumentException("ResultSet cannot be less than 1");
        _resultSet = value;
      }
    }

    [Category("Test Condition")]
    [DisplayName("Count")]
    [Description("Column Count")]
    public int Count
    {
      get { return _count; }

      set
      {
        if (value < 0)
          throw new ArgumentException("Count cannot be less than 0");
        _count = value;
      }
    }

    [Category("Test Condition")]
    [DisplayName("Comparison")]
    [Description("Comparison operator (<, >, =, or !)")]
    public string Comparison
    {
      get { return _comparison; }
      set
      {
        if (!"<>=!".Contains(value))
          throw new ArgumentException("Operation must be <, >, =, or !");
        _comparison = value;
      }
    }
    #endregion
  }
}

After creating a Release build, I copied the CustomTestConditions.dll assembly to the well known location. In my case, this was in the following folder:

C:Program Files (x86)Microsoft Visual Studio2019ProfessionalCommon7IDEExtensionsMicrosoftSQLDBTestConditions

Your folder location may be different depending on the version and edition of Visual Studio you are using.

I started up a new instance of Visual Studio with SSDT, loaded a SQL Server Database project, added a SQL Server Unit Test and started enjoying my new test conditions.

With the new Row Count (Advanced) test condition selected, I am able to set the comparison operator (less than, greater than, equals, or not equals) accordingly.

I created four SQL Server Unit Tests using the new Row Count (Advanced) test condition and discovered that I, in fact, have published more than 100 important blog posts. 🙂

The code can be found here. Enjoy.