In the previous article, Close those Loopholes in your Database Testing, I was discussing database testing in general. In this article Alex Styler and I will concentrate on database unit testing. Unit testing is becoming more and more common among C#, C++, and Java programmers, because it is very important in ensuring high quality of software. In the following article we shall demonstrate a simple way to test stored procedures that return result sets. In the next one we shall discuss a few more complex techniques used in testing database modifications.
Why use C# and NUnit for database unit testing?
Although there are alternatives, such as Team Edition for Database Professionals and TSQLUnit, the choice of C# and NUnit was natural for me. The reason is simple: being an old hand, I wanted to go for a commonly used, well-known, widespread approach. This is where C# fits the bill perfectly – in Windows programming universe C# is as common as it goes. And NUnit is a very commonly used library for development of unit tests in C# – it has been around for quite a while too. More to the point, if you are working with SQL Server, you are quite likely to be already familiar with C# or at least Visual Studio.
I am primarily a database developer. As such, I certainly prefer doing things in the database. For example, I am used to writing tests for my stored procedures in T-SQL – I have been doing that for years. However, I think that Visual Studio and NUnit are much better tools for the tedious task of thorough testing. Even though I am much more proficient in Transact SQL than in C#, I definitely prefer to write unit tests for my stored procedures using C# and NUnit.
Creating a unit test
Consider the following table, initial data, and existing stored procedure:
CREATE TABLE data.Employees(
EmployeeID INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Status VARCHAR(6) NOT NULL CONSTRAINT CHK_Employees_Status CHECK(Status IN('Active', 'Quit')),
HireDate DATETIME NOT NULL
)
GO
INSERT INTO data.Employees(EmployeeID, FirstName, LastName, Status, HireDate) VALUES(1, 'Sean', 'Hansen', 'Active', '20070105')
INSERT INTO data.Employees(EmployeeID, FirstName, LastName, Status, HireDate) VALUES(2, 'Don', 'Wang', 'Active', '20070106')
GO
CREATE PROCEDURE Readers.SelectEmployeesByLastName
@LastName VARCHAR(20)
AS
SELECT * FROM data.Employees WHERE LastName = @LastName
ORDER BY FirstName
GO
Suppose you need to write a unit test for that stored procedure. You might want to verify that your result set has correct structure (correct column names and types) and that is contains correct data. Because this is a very common task, it definitely makes sense to implement all these comparisons once, in a C# class, and use the library many times. For any reasonable project you will have at least hundreds of such tests, so implementing the comparison once in a class is definitely worth the effort. Once all the comparison logic has been implemented, your typical unit test is very short and simple:
private const bool bSetupMode = true;
[Test]
public void SelectEmployeesByLastName_Test()
{
string filename = “C:/Temp/SelectEmployeesByLastNameTest.xml”;
StoredProcedureTester tester = new
StoredProcedureTester( connection, SelectEmployeesByLastName” );
tester.SetInputParameter( “@lastname”, “Hansen” );
if (bSetupMode)
{
tester.OutputToFile( filename );
}
else
{
Assert.IsTrue(tester.CompareToFile( filename ));
}
}
When you set up your unit test, your result set (or result sets, if your stored procedure returns several ones) is saved into an XML file. All the information necessary for further comparison is saved – column names, column types, and the data. The XML file needs to be checked into your source control system, because it is part of your test harness. When you run your unit test, the result set is compared against the saved XML file. Everything is compared – column names, column types, and all the data Both OutputToFile and CompareToFile are methods of StoredProcedureTester class. We shall provide the implementation of the class in the next chapters.
Unit testing is not a substitute for other types of testing.
Unit tests by definition must run consistently, always producing the same results. As such, they do not expose all the problems. For instance, in some cases concurrency may affect your selects – they may run perfectly in a single-connection test environment but give you problems in a multi-user production one. For example, you can silently get incorrect results because of your choice of isolation level. One possible scenario is described in this article:
When Snapshot Isolation Helps and When It Hurts
In some cases a select can even blow up because of concurrency, as described in the following excellent blog entry in Craig Freedman’s WebLog: Query Failure with Read Uncommitted
Unfortunately, problems caused by concurrency may be intermittent – sometimes they can be reproduced, but not in exactly the same way every time you run your tests. In some cases if you stress test your selects properly, and your selects have problems, then your stress testing is likely to expose these problems sooner or later. This is why exposing problems in your selects caused by concurrency, however important it is, is mostly beyond the scope of unit testing and this article.
The Result Set Tester
In the “Creating Unit Test” chapter I utilized StoredProcedureTester class. In this and the next chapter AS will demonstrate how to implement this class. The following implementation has been stripped of all the bells and whistles – we left just enough code to demonstrate the technique in its simplest form. For example, a fully functional library should be able to compare result sets that are ordered on a non-unique column, such as FirstName, in one of the preceding examples. While our actual library can do all this and more, our example has been stripped of all this functionality.
We included a more powerful version of the testing library in the attached archive. The archive version abandons datasets in favor of speed and greater control over data comparisons. It might be a little harder to understand at first, but if you need greater control and flexibility, it might be worth your time to check it out.
Behind a seemingly simple unit test are the classes necessary to compare the result sets of your procedures. The first class, ResultSetTester, acts as an interface and provides most of the basic functionality for testing. It is constructed with a connection, implying a server and database, and the name of a stored procedure. You can then supply input and output parameters and either save the results to a file or compare the results to a saved file. Output parameters must be specified with a type and size and can be fetched after the procedure has run.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace UnitTestingTools
{
public class ResultSetTester
{
private readonly DataSet results = new DataSet();
private readonly SqlCommand storedProcedure;
public ResultSetTester(SqlConnection connection, string procedureName)
{
storedProcedure = new SqlCommand(procedureName, connection);
storedProcedure.CommandType = CommandType.StoredProcedure;
}
public bool CompareToFile(string filename)
{
RunProcedure();
DataSet other = new DataSet();
other.ReadXml(filename);
other.AcceptChanges();
return DataSetComparer.Compare(results, other);
}
public void OutputToFile(string filename)
{
RunProcedure();
results.WriteXml(filename, XmlWriteMode.WriteSchema);
}
public IEnumerable<KeyValuePair<string, object>> OuputParameters
{
get
{
Dictionary<string, object> outputParams = new Dictionary<string, object>();
foreach(SqlParameter p in storedProcedure.Parameters)
if(p.Direction == ParameterDirection.Output)
outputParams.Add(p.ParameterName, p.Value);
return outputParams;
}
}
public void SetInputParameter(string parameterName, object parameterValue)
{
SqlParameterCollection parameters = storedProcedure.Parameters;
if(parameters.Contains(parameterName))
parameters[parameterName] = new SqlParameter(parameterName, parameterValue);
else
parameters.AddWithValue(parameterName, parameterValue);
}
public void SetOutputParameter(string parameterName, SqlDbType type, int size)
{
SqlParameterCollection parameters = storedProcedure.Parameters;
if(parameters.Contains(parameterName))
parameters.RemoveAt(parameterName);
parameters.Add(parameterName, type);
parameters[parameterName].Direction = ParameterDirection.Output;
if(size > 0)
parameters[parameterName].Size = size;
}
private void RunProcedure()
{
results.Reset();
using(SqlDataAdapter da = new SqlDataAdapter(storedProcedure))
{
da.Fill(results);
}
for(int i = 0; i < results.Tables.Count; i++)
results.Tables[i].TableName = “Result Set ” + i;
results.AcceptChanges();
}
}
}
The DataSet Comparer
One of the most powerful of the ADO.NET objects is the DataSet. It is used in this example due to the ease of fetching results and the simplicity of storing the results in a file. However, due to varied opinions among developers on what defines the equality of data, the DataSet provides no means for data comparison.
This, however, allows you to define a means to compare data, directly determining how result sets are compared. In the example class below, DataSetComparer, the data must be exactly the same with the same ordering of result sets, rows, and columns. This works for all common data types, but would need custom code to work with user defined types or SqlVariant types that cannot be converted to strings. You can alter this comparer to ignore certain column types, allow varying order of rows, or compare only general trends of the data to fit your needs.
using System;
using System.Data;
namespace UnitTestingTools
{
internal static class DataSetComparer
{
internal static bool Compare(DataSet one, DataSet two)
{
if(one.Tables.Count != two.Tables.Count)
return false;
for(int i = 0; i < one.Tables.Count; i++)
if(!CompareTables(one.Tables[i], two.Tables[i]))
return false;
return true;
}
private static bool CompareTables(DataTable one, DataTable two)
{
if(one.Rows.Count != two.Rows.Count)
return false;
for(int i = 0; i < one.Rows.Count; i++)
if(!CompareRows(one.Rows[i], two.Rows[i]))
return false;
return true;
}
private static bool CompareRows(DataRow one, DataRow two)
{
if(one.ItemArray.Length != two.ItemArray.Length)
return false;
for(int i = 0; i < one.ItemArray.Length; i++)
if(!CompareItems(one.ItemArray[i], two.ItemArray[i]))
return false;
return true;
}
private static bool CompareItems(object value1, object value2)
{
if(value1.GetType() != value2.GetType())
return false;
if(value1 is DBNull)
return true;
if(value1 is DateTime)
return ((DateTime) value1).CompareTo((DateTime) value2)
== 0;
if(value1 is byte[])
{
if(((byte[]) value1).Length != ((byte[]) value2).Length)
return false;
for(int i = 0; i < ((byte[]) value1).Length; i++)
if(((byte[]) value1)[i] != ((byte[]) value2)[i])
return false;
return true;
}
return value1.ToString().Equals(value2.ToString());
}
}
}
Conclusions
If you are using C# and NUnit, you already have all the necessary tools to start covering your stored procedures with unit tests. Good luck!
Load comments