I've written a couple of articles on unit testing for stored procedures. The most recent was on the new tool from Microsoft, Visual Studio Team Edition for Database Professionals (referred to from here on as DBPro). It outlined how to use automated unit testing. One of the biggest strengths to the new testing environment comes from it's extensibility. You can actually write your own test conditions. One condition that we've been using in our internal unit testing for quite some time is the data comparison test (which you can see in action in my other article on unit testing). We needed to know that when we tuned a procedure, changing the join criteria, adding common table expressions, or any other changes to the internal logic of the query, the results from that procedure didn't change. We needed to know that the exact same data, data types, column order and sort order existed before and after any changes we made. Obviously this didn't apply when we actually changed inputs or outputs, but all other changes required this test. This type of test condition wasn't in DBPro, so I decided to add it.
I'll repeat the same review as last time. Automated unit testing is meant to support Agile development methodologies (though it can be rewarding as a general approach too). The iterative approach to database development and design requires test driven development to work and is best described in the works of Scott Ambler:Agile Database Techniques and Refactoring Databases. Microsoft has embraced the principles of Agile development internally, documented with a couple of different approaches here. These approaches are worth pursuing.
Assumptions
There are multiple working assumptions in effect. You've got a working a knowledge of TSQL, C# and Visual Studio. Biggest assumption, you'll be kind to me since I'm not a .NET expert. Please, if you spot issues with my methods let me know in the comments section. I'm also going to assume a nodding acquaintance with the Team Edition for Database Developers.
Code
Before we get started on the details of my implementation, let me acknowledge that without a blog entry by Sachin Rekhi I could never have done this. The core of what I'm going to present came from there. I just made the necessary changes to get the test condition in place that I wanted. My sincere appreciation to him for doing the work to make this possible. Any mistakes in the code below are mine and mine alone.
The rest of this is honestly as easy as it looks.
The condition that I set up relies on two result sets. One result set is obviously the stored procedure (trigger, function) being tested. The second result set can be either a straight select from another table or database, a second stored procedure, or by generating a temporary table, populating it with data and selecting from there. The code verifies that it actually got two result sets. It then checks that the result sets are equal in number of rows returned, columns, data types and the data in the columns.
The MSDN documentation covers all the basics on creating your own test condition. Please refer to this if you have any questions. First off, you do need to ensure that you add references to:
- Microsoft.VisualStudio.QualityTools.UnitTestFramework.dll
- Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.dll
Now we'll go through and set up the beginning of the code. Start with the 'using' statements and the necessary code to create the class.
using System;
using System.Data;
using System.Data.Common;
using System.ComponentModel;
using System.ComponentModel.Design;
using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting;
using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Conditions;
using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Configuration;
using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Exceptions;
using TestTools = Microsoft.VisualStudio.TestTools.UnitTesting;
namespace DatabaseUtilities.TestConditions.DataEqual
{
[DisplayName("Data Equal")]
public class DataEqual : TestCondition
Notice the DisplayName & string. This is how the test condition will show up in DBPro. Now I'm going to declare some variables to hold the result set that I want to compare and the result set that I want to compare it to. I also hold which batch I'm referring to in order to ensure that I'm looking at a single set of statements and not more than one. Right now it's hard-wired to only deal with a single batch. I create the class & load a set of default values for you.
private int _resultSet;
private int _batch;
private int _CompareSet;
public DataEqual()
{
_resultSet = 1;
_CompareSet = 2;
_batch = 1;
}
Now we override the Assert function. This is where the meat of the code is created. It took took the time to verify that we have a batch processed and that the batch returned result sets. If any of these things are problems, I simply throw an error specified as an AssertFailedException and describe what the problem encountered.
public override void Assert(DbConnection validationConnection, ExecutionResult[] results)
{
base.Assert(validationConnection, results);
//verify batch exists
if (results.Length < _batch)
throw new TestTools.AssertFailedException(String.Format("Batch {0} does not exist", _batch));
//get the results
ExecutionResult result = results[0];
//verify result set exists
if (result.DataSet.Tables.Count < _resultSet)
throw new TestTools.AssertFailedException(
String.Format("ResultSet {0} does not exist", _resultSet));
if (result.DataSet.Tables.Count < _CompareSet)
throw new TestTools.AssertFailedException(
String.Format("ResultSet {0} does not exist", _CompareSet));
I used the DataTable to get the information out of the results. I used the results to check the counts. I then walked the datatables a column at a time, verifying the data types. After that passed, I walked the data tables, column and row, to verify the actual data was the same.
//create datatables
DataTable testSet = result.DataSet.Tables[_resultSet-1];
DataTable compareSet = result.DataSet.Tables[_CompareSet-1];
//verify rows in each set
if (testSet.Rows.Count == 0)
throw new TestTools.AssertFailedException(
String.Format("Query {0} returned no rows", _resultSet));
if (compareSet.Rows.Count == 0)
throw new TestTools.AssertFailedException(
string.Format("Query {0} returned no rows", _CompareSet));
//verify
if (testSet.Rows.Count != compareSet.Rows.Count)
throw new TestTools.AssertFailedException(
String.Format("ResultSet {0}: {1} data did not match"
, _resultSet, _CompareSet));
else
{
//check the column data types
for (int i = 0; i < testSet.Columns.Count; i++)
{
if (testSet.Columns[i].ColumnName != compareSet.Columns[i].ColumnName)
throw new TestTools.AssertFailedException(
string.Format("Column Names {0} and {1} do not match"
, testSet.Columns[i].ColumnName
, compareSet.Columns[i].ColumnName));
if (testSet.Columns[i].DataType != compareSet.Columns[i].DataType)
throw new TestTools.AssertFailedException(
string.Format("Data type between {0} and {1} do not match"
, testSet.Columns[i].ColumnName
, compareSet.Columns[i].ColumnName));
}
//check the actual data in the row.
for (int j = 0; j < testSet.Rows.Count;j++)
for (int i = 0; i<testSet.Columns.Count;i++)
{
if (testSet.Rows[j][i].ToString() != compareSet.Rows[j][i].ToString() )
throw new TestTools.AssertFailedException(
string.Format("Data between {0}, {2}, and {1}, {3} do not match"
, testSet.Columns[i].ColumnName, compareSet.Columns[i].ColumnName
,testSet.Rows[j][i].ToString(),compareSet.Rows[j][i].ToString()));
That's pretty much it. Now to get it into DBPro, you have to make sure the DLL is installed and you have to use a little XML file to get it Visual Studio. Drop this into the folder "Microsoft Visual Studio 2005 Team Edition for Database Professionals - ENU." Restart Visual Studio and now when you navigate to your tests, you'll have a new test condition.
<?xml version="1.0" encoding="us-ascii"?>
<extensions assembly="TablesEqual, Version=1.0.0.0, Culture=neutral
, PublicKeyToken=d836ceac25a29688" version="1">
<extension type="TablesEqual.TablesEqual" enabled="true" />
</extensions>
Use
It's very easy to use. Simply run two queries in the test and use the Properties for the test condition to tell it which is the main result set and which is the compare result set (not that it matters right now). That's it. Be sure that the two queries do, initially, result in the same data, row & column order, etc., or the test will fail. Now you can refactor to your heart's content with an absolute assurance of a valid (or invalid) test. This is a sample of the SQL code for a test that can call to the test condition we just created:
DECLARE @rc int, @VarValue nvarchar(10) SELECT @rc = 0, @VarValue = 'AAAAAAA' --create a temp table DECLARE @Test as table( Col1 int, Col2 nvarchar(75) ) --load the temp table with data INSERT INTO @Test SELECT 42,'A valid result' --execute the proc I want to test and get the first result set EXEC @rc = dbo.MyProc @VarValue --and get the data from the temp table as the second result set SELECT * FROM @Test
Conclusion
I realize this is pretty simple and that several of you will probably implement the same set of code using only 3 lines or something. However, I'm pretty pleased with it. I've got a good mechanism in place to allow me to test my procedures and it works. One thing that I think I'll add would be the ability to pick a sort order and sort column for the test data. Any other suggestions for additions are welcome. I'll post any code updates so that others can benefit.
Here's the complete listing, no breaks or explanations:
using System;
using System.Data;
using System.Data.Common;
using System.ComponentModel;
using System.ComponentModel.Design;
using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting;
using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Conditions;
using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Configuration;
using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Exceptions;
using TestTools = Microsoft.VisualStudio.TestTools.UnitTesting;
namespace DatabaseUtilities.TestConditions.DataEqual
{
[DisplayName("Data Equal")]
public class DataEqual : TestCondition
{
private int _resultSet;
private int _batch;
private int _CompareSet;
public DataEqual()
{
_resultSet = 1;
_CompareSet = 2;
_batch = 1;
}
public override void Assert(DbConnection validationConnection, ExecutionResult[] results)
{
base.Assert(validationConnection, results);
//verify batch exists
if (results.Length < _batch)
throw new TestTools.AssertFailedException(
String.Format("Batch {0} does not exist", _batch));
//get the results
ExecutionResult result = results[0];
//verify result set exists
if (result.DataSet.Tables.Count < ResultSet)
throw new TestTools.AssertFailedException(
String.Format("ResultSet {0} does not exist", ResultSet));
//create datatables
DataTable testSet = result.DataSet.Tables[_resultSet-1];
DataTable compareSet = result.DataSet.Tables[_CompareSet-1];
//verify rows in each set
if (testSet.Rows.Count == 0)
throw new TestTools.AssertFailedException(
String.Format("Query {0} returned no rows", _resultSet));
if (compareSet.Rows.Count == 0)
throw new TestTools.AssertFailedException(
string.Format("Query {0} returned no rows", _CompareSet));
//verify
if (testSet.Rows.Count != compareSet.Rows.Count)
throw new TestTools.AssertFailedException(
String.Format("ResultSet {0}: {1} data did not match"
, _resultSet, _CompareSet));
else
{
//check the column data types
for (int i = 0; i < testSet.Columns.Count; i++)
{
if (testSet.Columns.ColumnName != compareSet.Columns.ColumnName)
throw new TestTools.AssertFailedException(
string.Format("Column Names {0} and {1} do not match"
, testSet.Columns.ColumnName
, compareSet.Columns.ColumnName));
if (testSet.Columns.DataType != compareSet.Columns.DataType)
throw new TestTools.AssertFailedException(
string.Format("Data type between {0} and {1} do not match"
, testSet.Columns.ColumnName
, compareSet.Columns.ColumnName));
}
//check the actual data in the row.
for (int j = 0; j<testSet.Rows.Count;j++)
{
for (int i = 0; i<testSet.Columns.Count;i++)
{
if (testSet.Rows[j].ToString() != compareSet.Rows[j].ToString() )
throw new TestTools.AssertFailedException(
string.Format("Data between {0}, {2}, and {1}, {3} do not match"
, testSet.Columns.ColumnName
, compareSet.Columns.ColumnName
,testSet.Rows[j].ToString(),compareSet.Rows[j].ToString()));
}
}
}
}
//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("Result set From Stored Proc being tested")]
public int ResultSet
{
get { return _resultSet; }
set
{
//basic validation
if (value < 1)
throw new ArgumentException("ResultSet cannot be less than 1");
_resultSet = value;
}
}
[Category("Test Condition")]
[DisplayName("CompareSet")]
[Description("Comparison data for test")]
public int CompareSet
{
get { return _CompareSet; }
set
{
if (value < 1)
throw new ArgumentException("CompareSet cannot be less than 1");
_CompareSet = value;
}
}
#endregion
}
}
