Click here to monitor SSC
  • Av rating:
  • Total votes: 40
  • Total comments: 17
Alex Kuznetsov and Alex Styler

Close those Loopholes - Testing Stored Procedures

20 August 2007

AK:

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:

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(EmployeeIDFirstNameLastNameStatusHireDateVALUES(1'Sean''Hansen''Active''20070105')
INSERT INTO data.Employees(EmployeeIDFirstNameLastNameStatusHireDateVALUES(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.

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.

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

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;
 
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

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!

Alex Kuznetsov and Alex Styler

Author profile:

Alex Kuznetsov has been working with databases for more than a decade. He is a SQL Server MVP. Currently he leads database development and optimizes database performance for a trading firm.

Alex Styler is currently a student at Carnegie Mellon University studying computer science and robotics. He is employed as a software engineering intern at a trading firm.

Search for other articles by Alex Kuznetsov and Alex Styler

Rate this article:   Avg rating: from a total of 40 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Attached Archive?
Posted by: troyehall (view profile)
Posted on: Monday, August 20, 2007 at 1:42 PM
Message:

This article stated "we included a more powerful version of the testing library in the attached archive." I cannot find the archive in question. Was this overlooked by the authors?

Ed: No, it was overlooked by the Editor. It is there now in the speech bubble


Subject: work with identities and timestamps?
Posted by: Arne (not signed in)
Posted on: Wednesday, August 22, 2007 at 6:47 PM
Message: It is very interesting, but it is not clear how can your comparisons work with identities and timestamps aka rowversions?

Subject: use IDENTITY_INSERT ON
Posted by: Alex Kuznetsov (view profile)
Posted on: Thursday, August 23, 2007 at 10:39 AM
Message: Arne,

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

Subject: RE: Instruction to play with code
Posted by: Alex Styler (view profile)
Posted on: Friday, August 31, 2007 at 1:35 PM
Message: 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.

Subject: Note
Posted by: Alex Styler (view profile)
Posted on: Friday, August 31, 2007 at 1:37 PM
Message: 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 05, 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?

Thanks.

Subject: archive
Posted by: JoStCharles (view profile)
Posted on: Wednesday, September 05, 2007 at 11:57 AM
Message: Found it. Sorry

Subject: No archive present
Posted by: karpetec (view profile)
Posted on: Saturday, September 29, 2007 at 7:57 AM
Message: Hi guys,

There's no archive present, all that you can get when you download the archive is a HTML file.

Subject: Great - but a couple of bugs FYI
Posted by: Tore (view profile)
Posted on: Tuesday, November 06, 2007 at 1:00 PM
Message: 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: Great - but a couple of bugs FYI
Posted by: Tore (view profile)
Posted on: Friday, November 09, 2007 at 4:31 PM
Message: 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.

Subject: Example Error
Posted by: Keith Hutchison (view profile)
Posted on: Monday, November 12, 2007 at 5:07 PM
Message: G'day

Using the following example as a guide and the files from the archive I had to change StoredprocedureTester to ResultSetTester before the library compiled.

Many thanks for the great article and source.

Keith Hutchison

[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 ));
}
}

Subject: Example Error
Posted by: Keith Hutchison (view profile)
Posted on: Monday, November 12, 2007 at 5:08 PM
Message: G'day

Using the following example as a guide and the files from the archive I had to change StoredprocedureTester to ResultSetTester before the library compiled.

Many thanks for the great article and source.

Keith Hutchison

[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 ));
}
}

Subject: Double Aggregate Errors
Posted by: Keith Hutchison (view profile)
Posted on: Monday, November 12, 2007 at 5:16 PM
Message: G'day

In testing two similar databases on two different machines we found that aggregate values were different after the 7th decimal spot.

We changed the following within the field for loop for
ResultSetParser.Parse(sqlCommand) to

object value = sqlReader.GetValue(i);

if (sqlReader.GetFieldType (i) == typeof(System.Double ) )
value = Math.Round((double)value, PRECISION );

if(!(value is DBNull))
row.AddColumn(new Column(fieldNames[i], value));

Subject: Open Source project
Posted by: Alex Kuznetsov (view profile)
Posted on: Tuesday, November 20, 2007 at 10:32 AM
Message: I have created an open source project on
http://code.google.com/p/sqlunittesting/source

Anyone willing to participate? Let me know.

Subject: Testing Stored Procedures also facilitate debugging
Posted by: codeguru (view profile)
Posted on: Friday, May 04, 2012 at 5:13 PM
Message: http://sharpgoals.com/dot-net-training-blog/post/2012/05/04/Debugging-Stored-Procedures.aspx

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.