Walkthrough: Using a Custom Test Condition to Verify the Results of a Stored Procedure
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 Team System Database Edition or Visual Studio Team System 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. For more information, see Add Reference Dialog Box.
Select the .NET tab.
In the Component Name column, locate the following components:
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 System.Linq; using System.Text; 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; 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 and DspCompatibilityCategory.
[DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.Any)] [DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.None)] [DisplayName("ResultSet Column Count")] public class ResultSetColumnCountCondition : TestCondition
Add the DisplayName attribute:
[DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.Any)] [DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.None)] [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 DataException 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 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)); }
Add the following method, which overrides String:
//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 System.Linq;
using System.Text;
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;
namespace ColumnCountCondition
{
[DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.Any)]
[DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.None)]
[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
- From 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>
From the File menu, click Save.
Next, you will copy the assembly information and the XML file to the Extensions directory. When Database Edition starts, it will identify any extensions in the <Microsoft Visual Studio 9.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 <Microsoft Visual Studio 9.0>\VSTSDB\Extensions\ directory.
Copy the ColumnCountCondition.dll assembly file from the My Documents\Visual Studio 2008\Projects\CustomConditions\CustomConditions\bin\Debug\ directory to the <Microsoft Visual Studio 9.0>\VSTSDB\Extensions\CustomConditions directory that you created.
Copy the ColumnCountCondition.Extensions.xml file from the My Documents\Visual Studio 2008\Projects\CustomConditions\CustomConditions\ directory to the <Microsoft Visual Studio 9.0>\VSTSDB\Extensions\ CustomConditions directory that you created.
Tip
A best practice is to put your extension assemblies in a folder in the <Microsoft Visual Studio 9.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 SQL Server project.
To start a new Visual Studio session and create a SQL Server project
Start a second session of Database Edition.
On the File menu, click New and then click Project.
In the New Project dialog box, under Project Types, click Database Projects and then click SQL Server 2008.
Under Templates, select 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.
Under Templates, click Database Unit Test.
Under Test Name, type SampleUnitTest.
Under Add to Test Project, select 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.
See Also
Tasks
How to: Create Test Conditions for the Database Unit Test Designer
How to: Register and Manage Feature Extensions
Concepts
Create and Define Database Unit Tests