Walkthrough: Using a custom test condition to verify the results of a stored procedure

In this feature extension walkthrough, you create a test condition, and you verify its functionality by creating a SQL Server unit test. The process includes creating a class library project for the test condition, and signing and installing it. If you already have a test condition that you want to update, see How to: Upgrade a Visual Studio 2010 Custom Test Condition from a Previous Release to SQL Server Data Tools.

This walkthrough illustrates the following tasks:

How to create a test condition.
How to sign the assembly with a strong name.
How to add the necessary references to the project.
How to build a test condition.
How to install the new test condition.
How to test the new test condition.

You must have Visual Studio 2010 or Visual Studio 2012 with the latest version of SQL Server Data Tools to complete this walkthrough. For more information, see Install SQL Server Data Tools (SSDT) for Visual Studio.

Create a custom test condition

First, you create a class library.

On the File menu, select New and then select Project.
In the New Project dialog box, under Project Types, select C#.
Under Templates, select Class Library.
In the Name text box, type ColumnCountCondition and then select OK.

Next, sign the project.

On the Project menu, select ColumnCountCondition Properties.
On the Signing tab, select the Sign the assembly check box.
In the Choose a strong name key file box, select <New...>.

The Create Strong Name Key dialog box appears.
In the Key file name box, type SampleKey.
Type and confirm a password, and then select OK. When you build your solution, the key file is used to sign the assembly.
On the File menu, select Save All.
On the Build menu, select Build Solution.

Next, you add the necessary references to the project.

In Solution Explorer, select the ColumnCountCondition project.
On the Project menu, select Add Reference to display the Add Reference dialog box.
Select the .NET tab.
In the Component Name column, locate and select the System.ComponentModel.Composition component. Select OK after you select the component.
Add the required assembly references. Right-click the project node and then select Add Reference. Select Browse and navigate to the C:\Program Files (x86)\Microsoft SQL Server\110\DAC\Bin folder. Choose Microsoft.Data.Tools.Schema.Sql.dll and select Add, then select OK.
On the Project menu, select Unload Project.
Right-click on the project in Solution Explorer and choose Edit <project name>.csproj.

Add the following Import statement after the import of Microsoft.CSharp.targets:

<Import Project="$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.Sql.UnitTesting.targets" Condition="'$(VisualStudioVersion)' == ''" />

<Import Project="$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.Sql.UnitTesting.targets" Condition="'$(VisualStudioVersion)' != ''" />

Save the file and close it. Right-click on the project in Solution Explorer and choose Reload Project.

The required references are shown under the References node of the project in Solution Explorer.

Create the ResultSetColumnCountCondition class

Now, you rename Class1 to ResultSetColumnCountCondition and derive it from testcondition. The ResultSetColumnCountCondition class is a simple test condition that verifies that the number of columns returned in the ResultSet. You can use this condition to make sure that the contract for a stored procedure is correct.

In Solution Explorer, right-click Class1.cs, select Rename, and type ResultSetColumnCountCondition.cs.
Select Yes to confirm renaming all the references to Class1.

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

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 ColumnCountCondition {
    public class ResultSetColumnCountCondition

Derive the class from testcondition:

public class ResultSetColumnCountCondition : TestCondition

Add ExportTestConditionAttribute. See How to: Create Test Conditions for the SQL Server Unit Test Designer for more information about UnitTesting.Conditions.ExportTestConditionAttribute.
```
[ExportTestCondition("ResultSet Column Count", typeof(ResultSetColumnCountCondition))]
    public class ResultSetColumnCountCondition : TestCondition
```

Create the member variables and the constructor:

        private int _resultSet;
        private int _count;
        private int _batch;

        public ResultSetColumnCountCondition() {
            _resultSet = 1;
            _count = 0;
            _batch = 1;
        }

Override the Assert method. The method includes arguments for IDbConnection, which represents the connection to the database, and SqlExecutionResult. The method uses DataSchemaException for error handling:

       //method you need to override
        //to perform the condition verification
        public override void Assert(DbConnection validationConnection, SqlExecutionResult[] results)
        {
            //call base for parameter validation
            base.Assert(validationConnection, results);

            //verify batch exists
            if (results.Length < _batch)
                throw new DataException(String.Format("Batch {0} does not exist", _batch));

            SqlExecutionResult result = results[_batch - 1];

            //verify resultset exists
            if (result.DataSet.Tables.Count < ResultSet)
                throw new DataException(String.Format("ResultSet {0} does not exist", ResultSet));

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

            //actual condition verification
            //verify resultset column count matches expected
            if (table.Columns.Count != Count)
                throw new DataException(String.Format(
                    "ResultSet {0}: {1} columns did not match the {2} columns expected",
                    ResultSet, table.Columns.Count, Count));
        }

Add the following method, which overrides the ToString method:

        //this method is called to provide the string shown in the
        //test conditions panel grid describing what the condition tests
        public override string ToString()
        {
            return String.Format(
                "Condition fails if ResultSet {0} does not contain {1} columns",
                ResultSet, Count);
        }

Add the following test condition properties by using the CategoryAttribute, DisplayNameAttribute, and DescriptionAttribute attributes:

        //below are the test condition properties
        //that are exposed to the user in the property browser
        //property specifying the resultset for which
        //you want to check the column count
        [Category("Test Condition")]
        [DisplayName("ResultSet")]
        [Description("ResultSet Number")]
        public int ResultSet
        {
            get { return _resultSet; }

            set
            {
                //basic validation
                if (value < 1)
                    throw new ArgumentException("ResultSet cannot be less than 1");

                _resultSet = value;
            }
        }

        //property specifying
        //expected column count
        [Category("Test Condition")]
        [DisplayName("Count")]
        [Description("Column Count")]
        public int Count
        {
            get { return _count; }

            set
            {
                //basic validation
                if (value < 0)
                    throw new ArgumentException("Count cannot be less than 0");

                _count = value;
            }
        }

The final code listing is:

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 ColumnCountCondition
{

    [ExportTestCondition("ResultSet Column Count", typeof(ResultSetColumnCountCondition))]
    public class ResultSetColumnCountCondition : TestCondition
    {
        private int _resultSet;
        private int _count;
        private int _batch;

        public ResultSetColumnCountCondition()
        {
            _resultSet = 1;
            _count = 0;
            _batch = 1;
        }

        //method you need to override
        //to perform the condition verification
        public override void Assert(DbConnection validationConnection, SqlExecutionResult[] results)
        {
            //call base for parameter validation
            base.Assert(validationConnection, results);

            //verify batch exists
            if (results.Length < _batch)
                throw new DataException(String.Format("Batch {0} does not exist", _batch));

            SqlExecutionResult result = results[_batch - 1];

            //verify resultset exists
            if (result.DataSet.Tables.Count < ResultSet)
                throw new DataException(String.Format("ResultSet {0} does not exist", ResultSet));

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

            //actual condition verification
            //verify resultset column count matches expected
            if (table.Columns.Count != Count)
                throw new DataException(String.Format(
                    "ResultSet {0}: {1} columns did not match the {2} columns expected",
                    ResultSet, table.Columns.Count, Count));
        }

        //this method is called to provide the string shown in the
        //test conditions panel grid describing what the condition tests
        public override string ToString()
        {
            return String.Format(
                "Condition fails if ResultSet {0} does not contain {1} columns",
                ResultSet, Count);
        }

        //below are the test condition properties
        //that are exposed to the user in the property browser
        //property specifying the resultset for which
        //you want to check the column count
        [Category("Test Condition")]
        [DisplayName("ResultSet")]
        [Description("ResultSet Number")]
        public int ResultSet
        {
            get { return _resultSet; }

            set
            {
                //basic validation
                if (value < 1)
                    throw new ArgumentException("ResultSet cannot be less than 1");

                _resultSet = value;
            }
        }

        //property specifying
        //expected column count
        [Category("Test Condition")]
        [DisplayName("Count")]
        [Description("Column Count")]
        public int Count
        {
            get { return _count; }

            set
            {
                //basic validation
                if (value < 0)
                    throw new ArgumentException("Count cannot be less than 0");

                _count = value;
            }
        }
    }
}

Next, we build the project.

Compile the project and install your test condition

On the Build menu, select Build Solution.

Next, you copy the assembly information to the Extensions directory. When Visual Studio starts, it identifies any extensions in the %ProgramFiles%\Microsoft Visual Studio <Version>\Common7\IDE\Extensions\Microsoft\SQLDB\TestConditions directory and subdirectories, and makes them available for use:

Copy the ColumnCountCondition.dll assembly file from the output directory to the %ProgramFiles%\Microsoft Visual Studio <Version>\Common7\IDE\Extensions\Microsoft\SQLDB\TestConditions directory.

By default, the path of your compiled .dll file is <YourSolutionPath>\<YourProjectPath>\bin\Debug or <YourSolutionPath>\<YourProjectPath>\bin\Release.

Next, you 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, select New and then select Project.
In the New Project dialog box, in the list of installed templates, select the SQL Server node.
In the details pane, select SQL Server Database Project.
In the Name text box, type SampleConditionDB and then select OK.

Next, we need to create a unit test. To create a SQL Server unit test inside a new test class:

On the Test menu, select New Test to display the Add New Test dialog box.

You can also open Solution Explorer, right-click a test project, point to Add, and then select New Test.
In the templates list, select SQL Server Unit Test.
In Test Name, type SampleUnitTest.
In Add to Test Project, select Create a new C# test project. Then, select OK to display the New Test Project dialog box.
Type SampleUnitTest for the project name.
Select Cancel to create the unit test without configuring the test project to use a database connection. Your blank test appears in the SQL Server Unit Test Designer. A C# source code file is added to the test project.

For more information about creating and configuring database unit tests with database connections, see How to: Create an Empty SQL Server Unit Test.
Select Click here to create to finish creating the unit test. You see the new test condition displaying in the SQL Server project.

Note

To use your custom test condition with existing unit test projects, at least one new SQL Server unit test class must be created. The required reference to your test condition assembly is added to your test project during test class creation.

To view the new test condition:

In the SQL Server Unit Test Designer, under Test Conditions, under the Name column, select the inconclusiveCondition1 test.
Select the Delete Test Condition toolbar button to remove the inconclusiveCondition1 test.
Select the Test Conditions dropdown list and select ResultSet Column Count.
Select the Add Test Condition toolbar button to add your custom test condition.
In the Properties window, configure the Count, Enabled, and ResultSet properties.

For more information, see How to: Add Test Conditions to SQL Server Unit Tests.

Custom Test Conditions for SQL Server Unit Tests

Feedback

Was this page helpful?

Last updated on 2025-09-10

Share via

Walkthrough: Using a custom test condition to verify the results of a stored procedure

Create a custom test condition

Create the ResultSetColumnCountCondition class

Compile the project and install your test condition

Related content

Feedback

Additional resources