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 Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\TestConditions
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.
Hi,
Thanks for the great post.
I ran into a little snack at the end. I added the .dll to the folder you suggest. But I do not see the new test conditions in my Visual Studio project . I closed VS and opened my solution, which has an SQL Server Database project. The solution already contains some unit test I wrote earlier. So could you help me?
I did set references to: Microsoft.Data.Tools.Components, Schema.Sql, Schema.Sql.UnitTesting, Schema.Sql.UnitTesting.Adapter
I’m sorry you ran into a snag. I’ll have to look into it and will post any updates.
I asked a colleague the create this custom test and all went well. So it is probably something with my VS installation on my laptop. I’m investigating that now.