Walkthrough: Using a Custom Test Condition to Verify the Results of a Stored Procedure

Article
02/04/2013

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

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 feature extension.
How to register the new feature extension.
How to test the new feature extension.

Prerequisites

You must have either Visual Studio Premium or Visual Studio Ultimate installed to complete this walkthrough.

Creating a Custom Test Condition

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 ColumnCountCondition and then click OK.

Next, you will sign the project.

To sign the project

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

The Create Strong Name Key dialog box appears.
In the Key file name box, type SampleKey.
Type and confirm a password, and then click OK.

When you build your solution, the key file is used to sign the assembly.
On the File menu, click Save All.
On the Build menu, click Build Solution.

Next, you will add the necessary references to the project.

To add applicable references to the project

In Solution Explorer, select the ColumnCountCondition project.
On the Project menu, click Add Reference.

The Add Reference dialog box opens.
Select the .NET tab.
In the Component Name column, locate the following components:
- Microsoft.Data.Schema
- Microsoft.Data.Schema.UnitTesting
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 ResultSetColumnCountCondition Class

Next, you will 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 is what you expect. You can use this condition to make sure that the contract for a stored procedure is correct.

To create a test condition class

In Solution Explorer, right-click Class1.cs, click Rename, and type ResultSetColumnCountCondition.cs.
Click 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.Collections.Generic;
using Microsoft.Data.Schema.UnitTesting;
using Microsoft.Data.Schema.UnitTesting.Conditions;
using Microsoft.Data.Schema.Extensibility;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using Microsoft.Data.Schema;

 
namespace ColumnCountCondition
{
    public class ResultSetColumnCountCondition

Derive the class from TestCondition:

    public class ResultSetColumnCountCondition : TestCondition

Add the DatabaseSchemaProviderCompatibilityAttribute attribute. For more information, see Generate Specialized Test Data with a Custom Data Generator.
```
[DatabaseSchemaProviderCompatibility(typeof(DatabaseSchemaProvider))]
   [DatabaseSchemaProviderCompatibility(null)]
    [DisplayName("ResultSet Column Count")]
    public class ResultSetColumnCountCondition : TestCondition
```
The test condition has both compatibility attributes so that:
- The condition is loaded when any database schema provider that inherits from DatabaseSchemaProvider is present. This handles the situation where the database unit test designer has a database schema provider context. If you wanted your test condition to be specific to SQL Server, you could instead specify SqlDatabaseSchemaProvider.
- The test condition is loaded when there is no database schema provider. This occurs when database unit testing loads extensions without a database schema provider.

Add the DisplayName attribute:

    [DatabaseSchemaProviderCompatibility(typeof(DatabaseSchemaProvider))]
        [DatabaseSchemaProviderCompatibility(null)]
    [DisplayName("ResultSet Column Count")]
    public class ResultSetColumnCountCondition : TestCondition

Create the member variables:

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

Create the constructor:

        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 ExecutionResult. The method uses DataSchemaException for error handling.

        //method you need to override
        //to perform the condition verification
        public override void Assert(DbConnection validationConnection, ExecutionResult[] results)
        {
            //call base for parameter validation
            base.Assert(validationConnection, results);
 
            //verify batch exists
            if (results.Length < _batch)
                throw new DataSchemaException(String.Format("Batch {0} does not exist", _batch));
 
            ExecutionResult result = results[_batch - 1];
 
            //verify resultset exists
            if (result.DataSet.Tables.Count < ResultSet)
                throw new DataSchemaException(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 DataSchemaException(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
        #region Properties
 
        //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;
            }
        }
 
        #endregion
    }
}

Your final code should appear as follows:

using System;
using System.Collections.Generic;
using Microsoft.Data.Schema.UnitTesting;
using Microsoft.Data.Schema.UnitTesting.Conditions;
using Microsoft.Data.Schema.Extensibility;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using Microsoft.Data.Schema;

namespace ColumnCountCondition
{
DatabaseSchemaProviderCompatibility(typeof(DatabaseSchemaProvider))]
        [DatabaseSchemaProviderCompatibility(null)]

    [DisplayName("ResultSet Column Count")]
    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, ExecutionResult[] 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)); 

            ExecutionResult 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
        #region Properties

        //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; 
            }
        }
 
        #endregion
    }
}

Next, you will build the project.

To build the project

On 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:
```
"ColumnCountCondition, 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 ColumnCountCondition 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 ColumnCountCondition.Extensions.xml and click the Add button.

The ColumnCountCondition.Extensions.xml file is added to the project in Solution Explorer.

Open the ColumnCountCondition.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="ColumnCountCondition.ResultSetColumnCountCondition" assembly="ColumnCountCondition, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn" enabled="true"/>
</extensions>

On the File menu, click Save.

Next, you will copy the assembly information and the XML file to the Extensions directory. When Visual Studio starts, it will identify any extensions in the %Program Files%\Microsoft Visual Studio 10.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 CustomConditions in the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\ directory.
Copy the ColumnCountCondition.dll assembly file from the output directory, by default, My Documents\Visual Studio 2010\Projects\CustomConditions\CustomConditions\bin\Debug\) to the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\CustomConditions directory that you created.
Copy the ColumnCountCondition.Extensions.xml file (by default, found in the My Documents\Visual Studio 2010\Projects\CustomConditions\CustomConditions\ directory) to the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\ CustomConditions directory that you created.

Tip

A best practice is to put your extension assemblies in a folder in the %Program Files%\Microsoft Visual Studio 10.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 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, click New and then click Project.
In the New Project dialog box, in the list of Installed Templates, expand the Database node and then click SQL Server.
In the details pane, click SQL Server 2008 Database Project.
In the Name text box, type SampleConditionDB and then click OK.

Next, you will create a unit test.

To create a database unit test inside a new test class

On the Test menu, click New Test.

Note

You can also open Solution Explorer, right-click a test project, point to Add, and then click New Test.

The Add New Test dialog box appears.
In the Templates list, click Database Unit Test.
In Test Name, type SampleUnitTest.
In Add to Test Project, click Create a new Visual C# test project.
Click OK.

The New Test Project dialog box appears.
Type SampleUnitTest for the project name.
Click Cancel to create the unit test without configuring the test project to use a database connection.

Note

For more information about creating and configuring database unit tests with database connections, see How to: Create an Empty Database Unit Test.

Your blank test appears in the Database Unit Test Designer. A Visual C# source code file is added to the test project.
Click Click here to create to finish creating the unit test.

Finally, you will see the new condition displaying in the SQL Server project.

To view the new condition

In the Database Unit Test Designer, under Test Conditions, under the Name column, click the inconclusiveCondition1 test.
Click the Delete Test Condition toolbar button to remove the inconclusiveCondition1 test.
Click the Test Conditions drop-down and select ResultSet Column Count.
Click 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 Database Unit Tests.