I wanted to walk you through just how you can go about creating your own custom test condition. This test condition will be added to the existing suite of in the box conditions and can be leveraged just like all of the other conditions inside the database unit testing designer.

 

Let me first motivate the sample test condition we are about to build. In the box we offer several “smoke test” types of test condition validation. For example, checking whether the returned resultset is nonempty allows you to quickly verify that the test script at least returns some rows. Similarly, the Row Count condition allows you to quickly check whether the appropriate number of rows were returned. However, almost as important as the data that is returned is the schema of the resultsets returned. You may want to create simple verification to ensure that the schema of the returned resultset is what we expect. In this example we’ll create a simple test condition that verifies that the number of columns returned by the resultset is what we expect. This is a quick smoke test to ensure that the contract for say a stored procedure is in fact intact.

 

Creating a custom test condition has three key steps

1.       Code the test condition class

2.       Deploy the test condition

3.       Use the test condition

 

Code the test condition class

1.      Create a new C# Class Library project in Visual Studio 2005 named SachTestConditions

(Of course you can use any .NET language, but here we walk you through a C# example)

2.      Add the following references to your project:

Microsoft.VisualStudio.QualityTools.UnitTestFramework.dll

 

Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.dll

(C:\Program Files\Microsoft Visual Studio 8\DBPro\Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.dll)

 

3.      Rename the Class1.cs file to ResultSetColumnCountCondition.cs

4.      Copy the following code into ResultSetColumnCountCondition.cs

 

//ResultSetColumnCountCondition

//Sample custom test condition

//

//Team Edition for Database Professionals

//Author: Sachin Rekhi

//Date: 8.22.06

 

using System;

using System.Collections.Generic;

using System.Text;

 

using System.Data;

using System.Data.Common;

using System.ComponentModel;

using System.ComponentModel.Design;

using TestTools = Microsoft.VisualStudio.TestTools.UnitTesting;

using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting;

using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Conditions;

 

namespace SachTestConditions

{

    [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 TestTools.AssertFailedException(String.Format("Batch {0} does not exist", _batch));

 

            ExecutionResult result = results[_batch - 1];

 

            //verify resultset exists

            if (result.DataSet.Tables.Count < ResultSet)

                throw new TestTools.AssertFailedException(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 TestTools.AssertFailedException(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

    }

}

 

Let’s analyze the code that we’ve just copied into our class. We started by adding the appropriate using statements. We then had our class inherit from the base TestCondition class.

 

We then added properties to our test condition. These are the properties that the user will be able to configure from the property browser inside of Visual Studio once they add your custom condition. For this example, we added 2 properties. The first is the ResultSet property, which allows the user to specify the number of the ResultSet they wish for this condition to check the column count on. The second property is the Count property, allowing the user to specify the expected column count. You can use the same attributes available to you through the component model. These are understood by our designer as well as by the Visual Studio Property Browser. As you can see here, we added 3 attributes – the category name to organize the properties under, the display name of the property, as well as a description. We also do some basic input validation which will be appropriately displayed to the user on an input error.

 

The most important next step is to override the Assert method. This method does the real work in validating that the expected condition is actually met. As you can see, the method provides you with two parameters. The key parameter is the results array. This returns a single array element for each batch executed. In V1, only a single batch is supported per test script, so your test condition will always interrogate the first array element. The most important property of the ExecutionResult object is the contained DataSet. This contains the returned resultsets for the test script. So for this particular condition we need to interrogate the appropriate data table in the dataset and verify that it contains the appropriate number of columns. Since you are returned a standard ADO.NET dataset, you can use this familiar construct to perform the validation. The code shown above shows you exactly how to do this for our scenario.

 

5.      You should now set your class library to be signed. You can do this by going to project properties, going to the Signing tab, and choosing Sign the assembly. Then specify a key or create a new one.

6.      You can now go ahead and build the test condition

 

Deploy the test condition

1.      The first thing you are going to want to do is add the test condition assembly to the GAC (Global Assembly Cache). You can do this using the gacutil.exe command. If both gacutil and your test condition assembly are in your path, you can use a command like the following. This will install (or force a re-install if it already exists) of the assembly.

 

gacutil /if SachTestConditions.dll

 

2.      The next step is to register the test condition. You do this by adding a new extensions.xml file in the appropriate location. The default location is the following:

 

C:\Program Files\Microsoft Visual Studio 8\DBPro

 

Add the file SachTestConditions.extensions.xml to the directory with the following contents: 

<?xml version="1.0" encoding="us-ascii"?>

<extensions assembly="SachTestConditions, Version=1.0.0.0, Culture=neutral, PublicKeyToken=<Public Key>" version="1" xmlns="urn:Microsoft.VisualStudio.TeamSystem.Data.Extensions" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:Microsoft.VisualStudio.TeamSystem.Data.Extensions

Microsoft.VisualStudio.TeamSystem.Data.Extensions.xsd">

  <extension type="SachTestConditions.ResultSetColumnCountCondition" enabled="true" />

</extensions>

Here we are registering the strongly named assembly and each of its contained conditions. In this example we have only our one condition. You must put the public key token for the signed assembly here as well. The easiest way to obtain the public key is to browse to the assembly directory in your Windows dir, find the binary that you just added to the GAC, and copy the public key token from there.

 

You can clearly automate this process and create a nice little setup tool if you wished to GAC the assembly and register it on the client machine. This way your entire organization can easily take advantage of your custom library of conditions. I’m not going to get into the details here, but you can use regular deployment technologies to accomplish this.

 

Use the test condition

§         Now we are ready to put the test condition to use. Make sure you close out of Visual Studio and re-open it. This is necessary because the test conditions are only loaded on initial load of Visual Studio and are not refreshed.

§         Add a database unit test to a new test project. When the database unit test designer appears, it should contain your custom test condition in the available test conditions drop-down.

§         Note: If this is the first time you are adding a database unit test to a test project, the appropriate assembly references to your custom conditions are automatically added. If the test project already contains database unit tests, you will need to manually add the reference to the custom test condition assembly.

§         Add the test condition to your test method and configure its properties.

§         And now you are all set to go!

 

I hope that gave you a comprehensive look at exactly what it takes to build your own custom test condition and add it to the library of built-in conditions.

 

We are very excited about this extensibility and hope it creates a rich community around database test verification.

 

Have questions? Let us know! We are hear to help J The forums are the best way to get your questions answered from the product group.

 

Sachin Rekhi