Close those Loopholes - Testing Stored Procedures

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?

AK:

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

AK:

Consider the following table, initial data, and existing stored procedure:

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]

publicvoid 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.

AK:

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

AK:

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.

AS:

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.

The DataSet Comparer

AS:

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;

namespaceUnitTestingTools

{

internalstaticclassDataSetComparer

{

internalstaticbool Compare(DataSet one, DataSet two)

{

if(one.Tables.Count != two.Tables.Count)

returnfalse;

for(int i = 0; i < one.Tables.Count; i++)

if(!CompareTables(one.Tables[i], two.Tables[i]))

returnfalse;

returntrue;

}

privatestaticbool CompareTables(DataTable one, DataTable two)

{

if(one.Rows.Count != two.Rows.Count)

returnfalse;

for(int i = 0; i < one.Rows.Count; i++)

if(!CompareRows(one.Rows[i], two.Rows[i]))

returnfalse;

returntrue;

}

privatestaticbool CompareRows(DataRow one, DataRow two)

{

if(one.ItemArray.Length != two.ItemArray.Length)

returnfalse;

for(int i = 0; i < one.ItemArray.Length; i++)

if(!CompareItems(one.ItemArray[i], two.ItemArray[i]))

returnfalse;

returntrue;

}

privatestaticbool CompareItems(object value1, object value2)

{

if(value1.GetType() != value2.GetType())

returnfalse;

if(value1 isDBNull)

returntrue;

if(value1 isDateTime)

return ((DateTime) value1).CompareTo((DateTime) value2)== 0;

if(value1 isbyte[])

{

if(((byte[]) value1).Length != ((byte[]) value2).Length)

returnfalse;

for(int i = 0; i < ((byte[]) value1).Length; i++)

if(((byte[]) value1)[i] != ((byte[]) value2)[i])

returnfalse;

returntrue;

}

return value1.ToString().Equals(value2.ToString());

}

}

}

Conclusions

AS:

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!

We use IDENTITY_INSERT ON when we populate the schema with test data in text fixture set up. Also we set the identity seed using DBCC CHECKIDENT before every test which inserts. This makes our identities consistent every time we run our unit test. Columns such as LastModifiedAt (DATETIME), LastModifiedBy (VARCHAR), as well as timestamp, are excluded from comparison altogether - that (exclusion) is very easy to implement.

Subject:

Instruction to play with code

Posted by:

Anonymous (not signed in)

Posted on:

Friday, August 31, 2007 at 10:23 AM

Message:

I downloaded the archive, opened it in VS but don't understand what should I do. Can you please provide some hints

The best place to get started is with the example code in the archive. Each class and it's role is decribed in this article. To get started, you would build the project into a class library; this will produce a *.dll file in your project/bin/debug (or /release) folder.

Then, in a seperate testing project, you would create a test fixture and test using the example provided in the article. Make sure you reference the library you compiled in the new project, as well as the NUnit library (depending on your experience, you may want to familiarize yourself with NUnit first!). A detail not shown in the example is the declaration of the connection (a SQLConnection object, which is well documented online).

You will create this connection in your test fixture setup or constructor and open a connection to your database. Then, you should be able to start messing aroud with the library. Start by testing a small, single result set, outputting the results to a file, then go inspect the file to get a feel for how it works. Your next step would be running it again, this time comparing the results to the file (you would switch the bSetupMode flag in the example).

If you need more detailed instruction you can comment further or email me directly.

Note that the archive also contains classes for the next article on testing modifications. If you're using the example version, ignore any classes you don't see in the article, you won't need these for your result set testing.

Subject:

Unable to locate the "attached archive"

Posted by:

Jo (not signed in)

Posted on:

Wednesday, September 5, 2007 at 11:54 AM

Message:

Hello,

RE: The attahced archive - "It is there now in the speech bubble.

I am sorry, I am not seeing it. Is there a link that could be used to access it?

This was a great article series and code. I picked up the code after this second installment, and built a whole database unit testing framework and UI around it that has been quite helpful around here.

Through use, I have found two bugs in the code:

1: ResultSetParser.Parse:If multiple result sets are produced, and the first result set is empty, i.e. if (!sqlReader.HasRows)subsequent result sets are ignored.

2: I'm not sure what triggered this, but saving a large result set for a test resulted in an incomplete file. I added two lines to the end of XmlFileAdapter.Write: writer.Flush(); writer.Close();but haven't attempted to reproduce the error with and without these lines.

This was a great article series and code. I picked up the code after this second installment, and built a whole database unit testing framework and UI around it that has been quite helpful around here.

Through use, I have found two bugs in the code:

1: ResultSetParser.Parse:If multiple result sets are produced, and the first result set is empty, i.e. if (!sqlReader.HasRows)subsequent result sets are ignored.

2: I'm not sure what triggered this, but saving a large result set for a test resulted in an incomplete file. I added two lines to the end of XmlFileAdapter.Write: writer.Flush(); writer.Close();but haven't attempted to reproduce the error with and without these lines.

Subject:

an open source project for the library

Posted by:

Alex K (not signed in)

Posted on:

Saturday, November 10, 2007 at 8:12 AM

Message:

With the agreement of both Alex Styler and the editor of this site, I created an open source project for the library. Currently it is pending approval on sourceforge.net. I will keep you informed.