20 August 2007

Close those Loopholes – Testing Stored Procedures

Alex and Alex continue their series of articles on 'Unit Testing' database development work with some examples of unit testing stored procedures.

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

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!

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

Downloads

This post has been viewed 49969 times – thanks for reading.

Tags: , , ,

  • Rate
    [Total: 42    Average: 4.5/5]
  • Share

Alex Kuznetsov and Alex Styler

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.

View all articles by Alex Kuznetsov and Alex Styler

  • troyehall

    Attached Archive?

    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

  • Arne

    work with identities and timestamps?
    It is very interesting, but it is not clear how can your comparisons work with identities and timestamps aka rowversions?

  • Alex Kuznetsov

    use IDENTITY_INSERT ON
    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.

  • Anonymous

    Instruction to play with code
    I downloaded the archive, opened it in VS but don’t understand what should I do. Can you please provide some hints

  • Alex Styler

    RE: Instruction to play with code
    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.

  • Alex Styler

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

  • Jo

    Unable to locate the “attached archive”
    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.

  • JoStCharles

    archive
    Found it. Sorry

  • karpetec

    No archive present
    Hi guys,

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

  • Tore

    Great – but a couple of bugs FYI
    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.

  • Tore

    Great – but a couple of bugs FYI
    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.

  • Alex K

    an open source project for the library
    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.

  • Keith Hutchison

    Example Error
    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 ));
    }
    }

  • Keith Hutchison

    Example Error
    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 ));
    }
    }

  • Keith Hutchison

    Double Aggregate Errors
    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));

  • Alex Kuznetsov

    Open Source project
    I have created an open source project on
    http://code.google.com/p/sqlunittesting/source

    Anyone willing to participate? Let me know.

  • codeguru