Unit testing update sprocs where more that one table is updated

2011/08/03

Introduction
The previous blog showed how to unit test an update sproc. The sample sproc used was relatively straight forward and it only updated one base table.

This blog will look at a slightly more complex scenario to testing an update that affects 2 base tables.

Background
AdventureWorks does not contain any samples sprocs that can be used. Therefore, a new sproc – HumanResources.uspUpdateEmployeeDetails – will be created and used as an example.

The purpose of the new sproc is to allow some basic details – FirstName, MiddleName, LastName, JobTitle – to be updated in one ‘busienss transaction. Two base tables – Person.Person (FirstName, MiddleName, LastName) and HumanResources.Employee(JobTitle) will be updated.

Testing that the sproc exists and that the schema is as expected
See the previous blog on how to do this,

The example tests in this series of blogs will be included as samples in future versions of the DBTestUnit download.

Creating the expected initial state test XML file
A similar approach to the previous blog will be used. But, data will be exported from both the HumanResources.Employee and Person.Person tables using the ExportDBDataAsXML tool.

The Person.Person table contains approx 20K rows. Ideally when unit testing, the size of test datasets should be much smaller than this. But, as the performance is not unduly affected (approx 10 secs to run) for the purposes of this blog it is easier to use the full dataset provided.

To export the data the following is added to the ExportDBDataAsXML tool config.

<!--********************-->
<!--SECTION FOR SPROCS-->
<!--********************-->
<Sprocs>

  <SQLObject>
    <FileName>TestData_uspUpdateEmployeeDetails_EmployeeInitialState</FileName>
    <Execute>YES</Execute>
    <SQLStatement>
      SELECT BusinessEntityID, NationalIDNumber, LoginID, OrganizationLevel, JobTitle, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate
      FROM HumanResources.Employee ORDER BY BusinessEntityID
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspUpdateEmployeeDetails_PersonInitialState</FileName>
    <Execute>YES</Execute>
    <SQLStatement>
      SELECT BusinessEntityID, PersonType, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion, AdditionalContactInfo, Demographics, rowguid, ModifiedDate
      FROM Person.Person ORDER BY BusinessEntityID
    </SQLStatement>
  </SQLObject>
 

The names of the XML/XSD files that will be created are:

TestData_uspUpdateEmployeeDetails_EmployeeInitialState and TestData_uspUpdateEmployeeDetails_PersonInitialState

When the tool is run it will output all the data in these tables as per the ‘SQLStatement’ in the config file.

The first record – where BusinessEntityID=1 – for each exported XML file is shown below:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>1</BusinessEntityID>
    <NationalIDNumber>295847285</NationalIDNumber>
    <LoginID>adventure-works\ken0</LoginID>
    <OrganizationLevel>0</OrganizationLevel>
    <JobTitle>Chief Executive Officer</JobTitle>
    <BirthDate>1963-03-01T00:00:00+00:00</BirthDate>
    <MaritalStatus>M</MaritalStatus>
    <Gender>F</Gender>
    <HireDate>2003-02-15T00:00:00+00:00</HireDate>
    <SalariedFlag>true</SalariedFlag>
    <VacationHours>99</VacationHours>
    <SickLeaveHours>69</SickLeaveHours>
    <CurrentFlag>true</CurrentFlag>
    <rowguid>f01251e5-96a3-448d-981e-0f99d789110d</rowguid>
    <ModifiedDate>2008-07-31T00:00:00+01:00</ModifiedDate>
  </Table>
 
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>1</BusinessEntityID>
    <PersonType>EM</PersonType>
    <NameStyle>false</NameStyle>
    <FirstName>Ken</FirstName>
    <MiddleName>J</MiddleName>
    <LastName>Sánchez</LastName>
    <EmailPromotion>0</EmailPromotion>
    <Demographics>&lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;&lt;TotalPurchaseYTD&gt;0&lt;/TotalPurchaseYTD&gt;&lt;/IndividualSurvey&gt;</Demographics>
    <rowguid>92c4279f-1207-48a3-8448-4636514eb7e2</rowguid>
    <ModifiedDate>2003-02-08T00:00:00+00:00</ModifiedDate>
  </Table>

Creating the expected post state test XML file
A copy of the files created above are taken and renamed as:
TestData_uspUpdateEmployeeDetails_EmployeePostState and TestData_uspUpdateEmployeeDetails_PersonPostState

The record with BusinessEntityID=1 will be updated as part of this test.

The following SQL statement will return the data that is expected to be updated by the new sproc:

SELECT FirstName, MiddleName, LastName, JobTitle
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p on e.BusinessEntityID = p.BusinessEntityID
WHERE p.BusinessEntityID = 1

The data returned is:

Ken,J,Sánchez,Chief Executive Officer

It should be updated to:

Kenneth,John,Sanchez,CEO

The following EXEC statement and update values will be run in the test:

EXEC EXEC HumanResources.uspUpdateEmployeeDetails
@BusinessEntityID=1
, @FirstName='Kenneth'
, @MiddleName='John'
, @Lastname='Sanchez'
, @JobTitle='CEO'
 

Both XML files are amended to include these ‘post update’ expected values to as follows (note only showing the first record and the rest of the file remains the same):

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>1</BusinessEntityID>
    <NationalIDNumber>295847285</NationalIDNumber>
    <LoginID>adventure-works\ken0</LoginID>
    <OrganizationLevel>0</OrganizationLevel>
    <JobTitle>CEO</JobTitle>
    <BirthDate>1963-03-01T00:00:00+00:00</BirthDate>
    <MaritalStatus>M</MaritalStatus>
    <Gender>F</Gender>
    <HireDate>2003-02-15T00:00:00+00:00</HireDate>
    <SalariedFlag>true</SalariedFlag>
    <VacationHours>99</VacationHours>
    <SickLeaveHours>69</SickLeaveHours>
    <CurrentFlag>true</CurrentFlag>
    <rowguid>f01251e5-96a3-448d-981e-0f99d789110d</rowguid>
    <ModifiedDate>2008-07-31T00:00:00+01:00</ModifiedDate>
  </Table>
 
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>1</BusinessEntityID>
    <PersonType>EM</PersonType>
    <NameStyle>false</NameStyle>
    <FirstName>Kenneth</FirstName>
    <MiddleName>John</MiddleName>
    <LastName>Sanchez</LastName>
    <EmailPromotion>0</EmailPromotion>
    <Demographics>&lt;IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"&gt;&lt;TotalPurchaseYTD&gt;0&lt;/TotalPurchaseYTD&gt;&lt;/IndividualSurvey&gt;</Demographics>
    <rowguid>92c4279f-1207-48a3-8448-4636514eb7e2</rowguid>
    <ModifiedDate>2003-02-08T00:00:00+00:00</ModifiedDate>
  </Table>

Writing the unit test that defines the requirement
Once the expected initial and post states have been defined the next step is to write the unit test.

The sample code below shows a data comparison test for this update sproc:

using System;
using MbUnit.Framework;
using DBTestUnit.Util;
using System.Data;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Functional
{
    [TestFixture]
    public class uspUpdateEmployeeDetails
    {
        string dbInstance = "AdventureWorks";
        string sprocTestFileDir = AppSettings.DirSprocExpectedResults();

        public uspUpdateEmployeeDetails() { }

        [RollBack]
        [Test]
        public void TestSprocUpdatesCorrectly()
        {
            //Expected data XML file names 
            string initialStateExpectedEmployeeDataFile = "TestData_uspUpdateEmployeeDetails_EmployeeInitialState";
            string initialStateExpectedPersonDataFile = "TestData_uspUpdateEmployeeDetails_PersonInitialState";
            string postStateExpectedEmployeeDataFile = "TestData_uspUpdateEmployeeDetails_EmployeePostState";
            string postStateExpectedPersonDataFile = "TestData_uspUpdateEmployeeDetails_PersonPostState";

            //SQL statement to return all data from the Employee table
            string sqlText_EmployeeData = "SELECT BusinessEntityID, NationalIDNumber, LoginID, OrganizationLevel, JobTitle,BirthDate,";
            sqlText_EmployeeData += " MaritalStatus, Gender,HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate";
            sqlText_EmployeeData += " FROM HumanResources.Employee order by BusinessEntityID";

            //SQL statement to return all data from the Person table
            string sqlText_PersonData = "SELECT BusinessEntityID, PersonType, NameStyle, Title, FirstName, MiddleName, LastName, Suffix,";
            sqlText_PersonData += " EmailPromotion, AdditionalContactInfo, Demographics, rowguid, ModifiedDate";
            sqlText_PersonData += " FROM Person.Person ORDER BY BusinessEntityID";

            //Update sproc exec SQL
            string sqlText_ExecSproc = "EXEC HumanResources.uspUpdateEmployeeDetails";
            sqlText_ExecSproc += " @BusinessEntityID=1";
            sqlText_ExecSproc += ", @FirstName='Kenneth'";
            sqlText_ExecSproc += ", @MiddleName='John'";
            sqlText_ExecSproc += ", @Lastname='Sanchez'";
            sqlText_ExecSproc += ", @JobTitle='CEO'";

            //BEFORE UPDATE TESTS - compare actual from table and expected initial states before running update
            bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, initialStateExpectedEmployeeDataFile, dbInstance, sqlText_EmployeeData);
            Assert.IsTrue(areTheSame, "Data in Employee table is not as expected BEFORE running update");

            areTheSame = DataSetComparer.Compare(sprocTestFileDir, initialStateExpectedPersonDataFile, dbInstance, sqlText_PersonData);
            Assert.IsTrue(areTheSame, "Data in Person table is not as expected BEFORE running update");

            //RUN UPDATE SPROC
            DALHelper.Execute(dbInstance, sqlText_ExecSproc);

            //AFTER UPDATE TESTS - compare actual from table and expected post states after running update
            areTheSame = DataSetComparer.Compare(sprocTestFileDir, postStateExpectedEmployeeDataFile, dbInstance, sqlText_EmployeeData);
            Assert.IsTrue(areTheSame, "Data in Employee table is not as expected AFTER running update");

            areTheSame = DataSetComparer.Compare(sprocTestFileDir, postStateExpectedPersonDataFile, dbInstance, sqlText_PersonData);
            Assert.IsTrue(areTheSame, "Data in Person table is not as expected AFTER running update");
        }
    }
}

 

The test is very similar to that outlined in the previous blog. But, it has to test two tables both before and after running the update.

When this test is run, it will fail as the sproc does not currently do anything. After running the ‘test EXEC statement’ the actual data in the tables have not changed and when compared to the expected post state XML files, they are different and therefore the test fails.

Making the test pass
A SQL script – as shown below – is created and run against the database.

At this point, only the ‘update’ functionality of the sproc is being tested. Note for simplicity no error or transaction handling is included (I hope to cover this in a future blog).

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('HumanResources.uspUpdateEmployeeDetails') AND type in (N'P', N'PC'))
DROP PROCEDURE [HumanResources].[uspUpdateEmployeeDetails]
GO

CREATE PROCEDURE HumanResources.uspUpdateEmployeeDetails
@BusinessEntityID int, 
@FirstName dbo.Name,
@MiddleName  dbo.Name,
@LastName  dbo.Name,
@JobTitle nvarchar (50)
AS

BEGIN

SET NOCOUNT ON

UPDATE Person.Person
SET FirstName = @FirstName
,MiddleName = @MiddleName
,LastName = @Lastname
WHERE BusinessEntityID =@BusinessEntityID

UPDATE HumanResources.Employee
SET JobTitle = @JobTitle
WHERE BusinessEntityID = @BusinessEntityID

END 

If the test is run again it should now pass as the sproc correctly updates both tables – changing the rows where BusinessEntityID=1 with the expected values.

Trouble shooting failing tests
Similar techniques can be used as per the previous blog.

What next
This blog has shown a way to test the functionality of a sproc that updates more than one base table.

So far, the tests have relied on comparing expected data – created as XML files – and actual data. The next blog will look at a different technique – running SQL statements – to unit test update sprocs.

Advertisements

Unit testing the functionality of an update stored procedures – part 2

2011/07/13

Introduction
The previous blog in this series looked at how to test that an update sproc existed and that its parameters and properties are as expected.

This blog will look at testing the functionality – how to ensure that it updates the underlying base table correctly.

It is assumed that you have read the previous blogs in this series:

Overview of the overall process
1. Ensure appropriate test data has been set up and that the expected initial state of the row to be updated is different to what it should be after the update sproc has run.
2. Use ExportDBDataAsXML tool to create an XML file of the ‘expected initial state’ (the data that should be present BEFORE running the update sproc).
3. Modify a copy of this file. Update the row which should be changed as part of the test with the expected values the sproc should update it with to create the ‘expected post state’ (the data that should be present AFTER running the update sproc).
4. Write the unit test. When first run it will fail as the sproc will not update the row.
5. Write the SQL script so that the sproc correctly updates the table.
6. Run the test again. It will pass if the ‘actual’ data in the table is the same when compared to the ‘expected’ data.

Creating the expected initial state test XML file
All the data in the HumanResources.Employee table – including the row that is going to be updated as part of the test – is exported as XML/XSD files using the ExportDBDataAsXML tool.

There are approx 290 rows in the table so the size of the table should not be an issue. Ideally, the size of test datasets should be smaller than this. But, this is used as this is the data provided in the sample database.

Add the following to the ExportDBDataAsXML tool config.

<!--********************-->
<!--SECTION FOR SPROCS-->
<!--********************-->
<Sprocs>

  <SQLObject>
    <FileName>TestData_uspUpdateEmployeePersonalInfo_InitialState</FileName>
    <Execute>YES</Execute>
    <SQLStatement>
      SELECT BusinessEntityID, NationalIDNumber, LoginID, OrganizationLevel, JobTitle, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate
      FROM HumanResources.Employee ORDER BY BusinessEntityID
    </SQLStatement>
  </SQLObject>

Note the name of the XML/XSD files that will be created – TestData_uspUpdateEmployeePersonalInfo_InitialState
and the SELECT ’SQLStatement’* used to export the data.

When the tool is run it will output all the data in the table as per the ‘SQLStatement’ into an XML file.

The first two ‘records’ of this file are shown below:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>1</BusinessEntityID>
    <NationalIDNumber>295847285</NationalIDNumber>
    <LoginID>adventure-works\ken0</LoginID>
    <OrganizationLevel>0</OrganizationLevel>
    <JobTitle>Chief Executive Officer</JobTitle>
    <BirthDate>1963-03-01T00:00:00+00:00</BirthDate>
    <MaritalStatus>M</MaritalStatus>
    <Gender>F</Gender>
    <HireDate>2003-02-15T00:00:00+00:00</HireDate>
    <SalariedFlag>true</SalariedFlag>
    <VacationHours>99</VacationHours>
    <SickLeaveHours>69</SickLeaveHours>
    <CurrentFlag>true</CurrentFlag>
    <rowguid>f01251e5-96a3-448d-981e-0f99d789110d</rowguid>
    <ModifiedDate>2008-07-31T00:00:00+01:00</ModifiedDate>
  </Table>
  <Table>
    <BusinessEntityID>2</BusinessEntityID>
    <NationalIDNumber>245797967</NationalIDNumber>
    <LoginID>adventure-works\terri0</LoginID>
    <OrganizationLevel>1</OrganizationLevel>
    <JobTitle>Vice President of Engineering</JobTitle>
    <BirthDate>1965-09-01T00:00:00+01:00</BirthDate>
    <MaritalStatus>S</MaritalStatus>
    <Gender>F</Gender>
    <HireDate>2002-03-03T00:00:00+00:00</HireDate>
    <SalariedFlag>true</SalariedFlag>
    <VacationHours>1</VacationHours>
    <SickLeaveHours>20</SickLeaveHours>
    <CurrentFlag>true</CurrentFlag>
    <rowguid>45e8f437-670d-4409-93cb-f9424a40d6ee</rowguid>
    <ModifiedDate>2008-07-31T00:00:00+01:00</ModifiedDate>
  </Table>

The initial state of the table is recorded and tested as it is very easy to forget to ‘rollback’ the changes of an update, insert or delete unit test. If rollback is not carried out then tests will potentially pass even if the sproc is not updating correctly. This is a relatively quick way to check that the pre-conditions are as expected.

*The SELECT statement does not include the column ‘OrganizationNode’ as it uses a new data type (as per SQL 2K8) ‘hierarchyid’. This data type is currently excluded as it causes issues with DBTestUnit comparison tests.

Creating the expected post state test XML file
A copy of the XML file created above is taken and renamed as:

TestData_uspUpdateEmployeePersonalInfo_PostState.xml

The first record with – BusinessEntityID=1 – will be updated as part of the test.

The following EXEC statement and ‘update’ values will be run in the test:

EXEC HumanResources.uspUpdateEmployeePersonalInfo 
@ BusinessEntityID=1
, @NationalIDNumber =11111111
, @BirthDate='1960-01-01'
, @MaritalStatus='M'
, @Gender='F'

Each parameter has a different value to the original initial state of the record. This is to ensure that each parameter correctly updates its associated base table column.

‘TestData_uspUpdateEmployeePersonalInfo_PostState.xml’ needs to be amended to include these post update expected values. So, the first record is manually updated, changing it to the values expected as per the ‘EXEC’ above (note ALL the other ‘records’ should remain unchanged).

The modified record is shown below:

<Table>
    <BusinessEntityID>1</BusinessEntityID>
    <NationalIDNumber>11111111</NationalIDNumber>
    <LoginID>adventure-works\ken0</LoginID>
    <OrganizationLevel>0</OrganizationLevel>
    <JobTitle>Chief Executive Officer</JobTitle>
    <BirthDate>1960-01-01T00:00:00+00:00</BirthDate>
    <MaritalStatus>M</MaritalStatus>
    <Gender>F</Gender>
    <HireDate>2003-02-15T00:00:00+00:00</HireDate>
    <SalariedFlag>true</SalariedFlag>
    <VacationHours>99</VacationHours>
    <SickLeaveHours>69</SickLeaveHours>
    <CurrentFlag>true</CurrentFlag>
    <rowguid>f01251e5-96a3-448d-981e-0f99d789110d</rowguid>
    <ModifiedDate>2008-07-31T00:00:00+01:00</ModifiedDate>
  </Table>

Writing the unit test that defines the requirement
Once the expected initial and post states have been defined the next step is to write the unit test.

The sample code below shows a data comparison test for this update sproc:

using System;
using MbUnit.Framework;
using DBTestUnit.Util;
using System.Data;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Functional
{
    [TestFixture]
    public class uspUpdateEmployeePersonalInfo
    {
        string dbInstance = "AdventureWorks";
        string sprocTestFileDir = AppSettings.DirSprocExpectedResults();

        public uspUpdateEmployeePersonalInfo() { }

        [RollBack]
        [Test]
        public void TestSprocUpdatesCorrectly()
        {
            //Expected data XML file names 
            string initialStateExpectedDataFile = "TestData_uspUpdateEmployeePersonalInfo_InitialState";
            string postStateExpectedDataFile = "TestData_uspUpdateEmployeePersonalInfo_PostState";

            //SQL statement to return all data from the table
            string sqlText_TestData = "SELECT BusinessEntityID, NationalIDNumber, LoginID, OrganizationLevel, JobTitle, BirthDate, MaritalStatus, Gender,";
            sqlText_TestData += "HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate";
            sqlText_TestData += " FROM HumanResources.Employee order by BusinessEntityID";

            //Update sproc exec
            string sqlText_ExecSproc = "EXEC HumanResources.uspUpdateEmployeePersonalInfo 1,11111111,'1960-01-01','M','F'";

            //Compare actual from table and expected initial states BEFORE update
            bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, initialStateExpectedDataFile, dbInstance, sqlText_TestData);
            Assert.IsTrue(areTheSame, "Data in table is not as expected BEFORE running update");

            //Run update sproc
            DALHelper.Execute(dbInstance, sqlText_ExecSproc);

            //Compare actual from table and expected post states AFTER update
            areTheSame = DataSetComparer.Compare(sprocTestFileDir, postStateExpectedDataFile, dbInstance, sqlText_TestData);
            Assert.IsTrue(areTheSame, "Data in table is not as expected AFTER running update");
        }
    }
}

 

A number of things to note from the sample test:

1. It follows the steps outlined in the intro to this series:

  • Test the initial state
  • Run the sproc
  • Test the post state

2. It uses the MBUnit ‘[RollBack]’ attribute. This will run the test under a transaction and will roll it back once it has completed. This allows the test to be run multiple times.

3. The SQL SELECT set in the variable ‘sqlText_TestData’ is the same as that used in the ExportDBDataAsXML tool config to create the test XML file.

4. As seen in previous blogs, the DBTestUnit method ‘DataSetComparer.Compare’ is used.

When this test is run it will fail as the sproc does not currently do anything. After running the ‘test EXEC statement’ the actual data in the table has not changed and when compared to the expected post state XML file, it is different and so the test fails.

Making the test pass
A script – as shown below – is created and run against the database.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HumanResources].[uspUpdateEmployeePersonalInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
GO

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
@BusinessEntityID [int], 
@NationalIDNumber [nvarchar](15), 
@BirthDate [date], 
@MaritalStatus [nchar](1), 
@Gender [nchar](1)
AS

BEGIN

SET NOCOUNT ON;

UPDATE [HumanResources].[Employee] 
SET [NationalIDNumber] = @NationalIDNumber 
,[BirthDate] = @BirthDate 
,[MaritalStatus] = @MaritalStatus 
,[Gender] = @Gender 
WHERE [BusinessEntityID] = @BusinessEntityID;

END

If the test is run again – it should now pass as the sproc correctly updates the table – changing the row where BusinessEntityID=1 with the expected values.

Trouble shooting failing tests
These types of tests can help identify a number of errors that are easily made when carrying out initial design or when refactoring a sproc. It is very easy to update a column with the wrong parameter or to update incorrect rows by making a mistake with the ‘WHERE’ clause.

For example, say a mistake is made with the parameters as shown below:

UPDATE [HumanResources].[Employee] 
SET [NationalIDNumber] = @MaritalStatus 
,[BirthDate] = @BirthDate 
,[MaritalStatus] = @MaritalStatus 
,[Gender] =  @Gender
WHERE [BusinessEntityID] = @BusinessEntityID;

The column ‘NationalIDNumber’ will be incorrectly updated with the value set in the parameter ‘@MaritalStatus’

If the unit test is run again it will fail with an error as shown below.

In a simple case like this, trouble shooting might be as straight forward as visually inspecting the sproc creation script. But, in cases where the sproc is more complex identifying the issue via inspection might be difficult. ‘Seeing’ the difference between the actual and expected data can be useful.

In the unit test above the actual data is not persisted. If required, it can be persisted by adding the following code (under comment ‘//Persist actual data in table after doing update’’) as shown below:


            //Run update sproc
            DALHelper.Execute(dbInstance, sqlText_ExecSproc);

            //Persist actual data in table after doing update
            DataSet ds = DALHelper.ExecuteDataSet(dbInstance, sqlText_TestData);
            string postStateActualDataFile = sprocTestFileDir + postStateExpectedDataFile + "ActualData.xml";
            ds.WriteXml(postStateActualDataFile);

            //Compare actual from table and expected post states AFTER update
            areTheSame = DataSetComparer.Compare(sprocTestFileDir, postStateExpectedDataFile, dbInstance, sqlText_TestData);
            Assert.IsTrue(areTheSame, "Data in table is not as expected AFTER running update");
        }

When this test is run – the inserted code will create an XML file ‘TestData_uspUpdateEmployeePersonalInfo_PostUpdate_ActualData.xml’ in the same directory as the other XML files.

It will persist the actual data in the table after running the update sproc.

This file can then be compared to the expected XML file – ‘TestData_uspUpdateEmployeePersonalInfo_PostUpdate.xml’ – using a diff tool.

The result of this comparison is shown below:

This highlights the fact that the ‘NationalIDNumber’ element is different. Knowing this should make it easier to resolve issues with the sproc implementation.

Why not persist the actual data all the time as part of the ‘standard’ unit test?

As reviewing/troubleshooting the output of the diff process is a manual step and the overhead of adding this code on an ‘ad hoc’ basis is not too great, I treat it as an exception so I don’t include it as standard into the unit test. If required, just include it as part of the test.

What next
This blog has shown a way to test the functionality of an update sproc.

The example sproc is only updating one base table and is relatively simple. The next blog in this series will look at testing more complex scenarios – for example – where two or more tables are updated by a sproc.


Unit testing update stored procedures – part 1

2011/06/29

Introduction
This is the first in a series of blogs that will look at unit testing an ‘update’ sproc using taking a TDD approach.

Testing that the sproc exists and that it has it’s expected properties will be covered – following steps 1 and 2 as per the Database unit testing patterns.

What are the requirements of the sproc
HumanResources.uspUpdateEmployeePersonalInfo from the AdventureWorks2008R2 database will be used as an example.

It updates the HumanResources.Employee table with the values specified in the input parameters for a specified Employee – using the primary key BusinessEntityID.

The SQL script to create it is shown below:


CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
@BusinessEntityID [int], 
@NationalIDNumber [nvarchar](15), 
@BirthDate [datetime], 
@MaritalStatus [nchar](1), 
@Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
UPDATE [HumanResources].[Employee] 
SET [NationalIDNumber] = @NationalIDNumber 
,[BirthDate] = @BirthDate 
,[MaritalStatus] = @MaritalStatus 
,[Gender] = @Gender 
WHERE [BusinessEntityID] = @BusinessEntityID;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspLogError];
END CATCH;
END;

This sproc will be dropped from the database and recreated from scratch – but taking a TDD type approach.

The ‘end game’ is to recreate the sproc as above (with a couple of minor changes that I will mention along the way).

The first thing is to run the following script to delete it from the test database:

DROP PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo

Step 1 Testing that the sproc exists
A similar approach to that in Unit testing ‘Select’ stored procedures – part 1 is used.

The unit test class – ..\Tests\DatabaseTest.cs – tests for the existence of all expected SQL objects that should be in the database.

As the sproc was previously present its name is already in the list of expected sprocs (‘expectedSprocList’ and the ‘expectedSprocCount’ is already 11).

When this tests are run those related to sproc counts fail as the sproc was deleted so the count is down to 10.

To get the test to pass the following script is run:

CREATE PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo
AS
BEGIN
SET NOCOUNT ON;
END
 

The tests now pass.

Step 2 Testing it’s properties
A similar approach to that in Unit testing ‘Select’ stored procedures – part 2 is be used.

Take a copy of one of the sample unit test classes included in the DBTestUnit download and rename it to uspUpdateEmployeePersonalInfo_Schema.

Update it appropriately so that the unit test class looks like the following:


using System;
using MbUnit.Framework;
using DBTestUnit.InfoSchema;
using DBTestUnit.UnitTestBaseClass.MSSQL;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Schema
{
    [TestFixture]
    public class uspUpdateEmployeePersonalInfo : SprocTestBase
    {
        public uspUpdateEmployeePersonalInfo()
        {
            dbInstance = "AdventureWorks";
            schema = "HumanResources";
            sprocName = "uspUpdateEmployeePersonalInfo";
            sproc = new Sproc(dbInstance, schema, sprocName);
            expectedInputCount = 5;
            expectedInputList = "@BusinessEntityID,@NationalIDNumber,@BirthDate,@MaritalStatus,@Gender";
            expectedOutputCount = 0;
            expectedOutputList = "";
        }
        [RowTest]
        [Row("@BusinessEntityID ", "1,IN,int,N/A")]
        [Row("@NationalIDNumber ", "2,IN,nvarchar,15")]
        [Row("@BirthDate", "3,IN,date,N/A")]
        [Row("@MaritalStatus", "4,IN,nchar,1")]
        [Row("@Gender", "5,IN,nchar,1")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
        {
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");
        }

        [RowTest]
        [Row("@BusinessEntityID", "HumanResources.Employee", "BusinessEntityID")]
        [Row("@NationalIDNumber", "HumanResources.Employee", "NationalIDNumber")]
        [Row("@BirthDate", "HumanResources.Employee", "BirthDate")]
        [Row("@MaritalStatus", "HumanResources.Employee", "MaritalStatus")]
        [Row("@Gender", "HumanResources.Employee", "Gender")]
        public void T06_ParameterIsTheSameDataTypeAndLength(string paramName, string tableName, string columnName)
        {
            bool areTheSame = sproc.ParameterIsSameAsColumn(paramName, tableName, columnName);
            Assert.IsTrue(areTheSame, "Parameter and column are not the same");
        }
    }
}

This will test that the sproc’s parameters are as expected – correct number of parameters and properties.

It will also check that the parameters have the same data types as the base table columns that they will update. It is very easy to make a mistake and not have the parameters exactly match the columns (see this blog for more detail).

* The paramater ‘@BirthDate’ in the original sproc – see SQL script above – has a data type of ‘datetime’ whereas the underlying base table column HumanResources.Employee has a type of ‘date’. The sproc created in this series of blogs will have ‘@BirthDate’ as ‘date’.

Compile and run the tests. They will fail as the sproc does not currently have any parameters.

To make it pass the tests the following script is run:


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'HumanResources.uspUpdateEmployeePersonalInfo') AND type in (N'P', N'PC'))
DROP PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo
GO

CREATE PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo
@BusinessEntityID [int], 
@NationalIDNumber [nvarchar](15), 
@BirthDate [date], 
@MaritalStatus [nchar](1), 
@Gender [nchar](1)
AS

BEGIN

SET NOCOUNT ON;


END
 

Run the test again and they now pass as the sproc meets the expectations/requirements as per the tests.

What next
Steps 1 and 2 as per the Database unit testing patterns have been carried out and an initial sproc has been created.

The next blog in this series will look at Step 3 – testing the functionality of the sproc – ensuring that it updates the base table as expected.


Unit testing insert, delete and update stored procedures – intro

2011/06/27

Up to now the previous blogs on unit testing sprocs have focused on those that output data – ‘Select’ sprocs. Over the next few weeks I intend to write a series of blogs that will look at testing sprocs that carry out other types of data manipulation.

The steps outlined in Database unit testing patterns will be followed for testing delete, insert and update sprocs. But as steps 1,2 and 4 are effectively the same as outlined previously, the focus will be on Step 3 – testing the functionality of the sproc.

Does the sproc delete/insert/update as expected?

Over the series a functional test pattern will emerge for testing all three types of sprocs.

This can be outlined as:

1. Test initial state is as expected
For example, if testing an update sproc ensure that the row(s) to be updated has the expected initial values (ie it hasn’t already been updated by a previous test and not ‘rolled back’).

2. Run the sproc

3. Test post state is as expected
For example, after running an update sproc ensure that the row(s) has been updated correctly AND it hasn’t inadvertantly effected any other rows.

I will give examples of two different approaches that can be used to do the steps outlined above:

A. Using the ExportDBDataAsXML tool to create expected pre and post state XML files that can that can be compared to ‘actuals’.

B. Using ‘ad hoc’ SQL commands to test checksums, row counts and values in rows for both pre and post states.

The series will start with a relatively straight forward example of an update sproc – taken from Microsoft’s AdventureWorks2008R2 – which updates a single table. This will then be extended to look at how to handle more complex scenarios, such as where 2 or more base tables are involved.


Using ad hoc SQL commands in DBTestUnit tests

2011/06/20

When writing database tests you will find that you need to excute a SQL statement as part of the test.

For example, you might want to check the number of rows in a table before and after running an ‘insert’ sproc or to execute the sproc from your C# tests.

The DBTestUnit.Util.DALHelper class provides three methods that allow SQL commands to be run against your database.

These are:

  • ExecuteScalar
  • ExecuteDataSet
  • Execute

All take two strings as parameters – the dbInstance (defines which db to run the SQL command against) and the SQL text to run.

Which one to use depends on the expected output:

  • No value to be returned.

    For example to carry out an insert of a test record into a table:

    string sqlText = "INSERT INTO HumanResources.Department (Name, GroupName) VALUES ('TestDeptName', 'TestGroupName')";
    DBTestUnit.Util.DALHelper.Execute(dbInstance, sqlText);
    
  • Scalar value to be returned.

    For example, to get the row count of a table:

    string sqlText = "SELECT COUNT(*) FROM HumanResources.Department";
    int rowCount = System.Convert.ToInt32(DALHelper.ExecuteScalar(dbInstance, sqlText));
    
  • Dataset to be returned
    sqlText = "SELECT * FROM  HumanResources.Department";
    DataSet ds =DBTestUnit.Util.DALHelper.ExecuteDataSet(dbInstance, sqlText);
    


Unit testing the data outputted by a stored procedure

2011/05/17

Introduction
The previous blog in this series gave a general introduction into taking a TDD approach when testing the expected data outputs for a new sproc.

This blog will look into this in more area detail – in particular looking at:

  • Testing each query filter in a ‘SELECT’ statement.
  • Setting up appropriate test data to do this.

It is assumed that you have read the previous blogs in this series.

Note
As mentioned previously there are two areas to think about when testing the data outputted by a SQL object:

A) The data elements/columns returned.

B) The query filters required to change the data set returned.

The previous blog whilst being good for A) there are outstanding issues with part B).

Only one test was developed and for the chosen department the sproc returns the expected data set ie it returns ‘current employees for a given department’.

If another department is chosen, eg DepartmentId=10, and the sproc executed as shown:

EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=10

As can be seen in the image below, the sproc incorrectly returns people that have left the department as seen in the first row (EndDate’ is in the past).

The department chosen for the initial test in the previous blog just happened to have a ‘nice set’ of test data ie there were no employees that had left that department.

This shows the importance of having appropriate test coverage.

Background scenario
At this point there is a need to think about what is meant by ‘current employees for a given department’ and to carry out some further analysis of the current AdventureWorks database design.

The system* is required to record when a person has:

  • Left the firm.
  • Left an individual department to join another one eg an internal department transfer.
  • Indicated that they will carry out an internal department transfer in the future.

In general terms the sproc should only return those people:

A) Who have not left the firm.
B) Who are currently working for a given department.

The data set returned by the sproc is created by joining the HumanResources.Employees and HumanResources.EmployeeDepartmentHistory tables and filtering by DepartmentId. Effectively this returns ALL people that have ever worked for a given department.

Looking at these two tables in a little more detail.

  • HumanResources.Employees. Contains a column ‘CurrentFlag’. A value of ‘1’ indicates that a person is still with the company – ‘0’ they have left.
  • HumanResources.EmployeeDepartmentHistory. This contains two columns of interest – ‘StartDate’ and ‘EndDate’. If ‘EndDate’ is ‘Null’ or the value is in the future it indicates that the person is still with the department.

For a given department what data should be included and conversely what should be excluded in the dataset returned?

This can be broken down into 4 areas (a short-hand description is given first and will be used throughout the rest of this blog):

  • ‘Exclude company leavers’.Only include people currently with the company – exclude anyone who has left (indicated by the ‘CurrentFlag’).
  • ‘Exclude department leavers’**Only include people currently working for the department – exclude those that have left started the department (indicated by the ‘EndDate’ being in the past).
  • ‘Exclude future department joiners’Only include people currently working for the department – exclude those that will join in the future (indicated by the ‘StartDate’ being in the future).
  • ‘Include future department leavers’**Include people currently working for the department – even if they will leave in department in the future (indicated by the ‘EndDate’ being in the future).

Each one the above will be tested.

* The purpose of this blog is to show how detailed data output testing can be carried out using DBTestUnit. Therefore I am creating a scenario where multiple ‘query filters’ are required.

** Due to ‘EndDate’ allowing nulls – both these scenarios need to be tested.

Setting up test data
In the previous blog the existing AdventureWorks data was used. To explicitly test each of the scenarios outlined above further test data needs to be created.

4 new departments are created – 1 new department for each ‘query filter’ to test. Each new department will also have 2 new people created. One row should be returned and one shouldn’t. This setup ensures that the data sets returned by tests are exclusive with no risk of overlap.

The four new departments are shown below:

The people created for each new department and the requirements that they will test are shown below.

‘Exclude company leavers’ – DepartmentID 17

The first person – with ID=20778 – CurrentFlag=0 indicates that they have left the firm. This row should not be returned.

Note that their ‘EndDate’ has deliberately been left as ‘Null’. This ensures only testing of the fact that they have left the firm and not that they have left the department.

‘Exclude department leavers’ – DepartmentID=18

The first person – ID=20780 – ‘EndDate’ in the past indicates that they have left the department. This row should not be returned.

‘Exclude future department joiners’ – DepartmentID=19

The first person – ID=20782 – ‘StartDate’ in the future – indicates that they have not joined the department yet.This row should not be returned.

‘Include future department leavers’ – DepartmentID=20

The first person – ID=20784 – ‘EndDate’ in the future. Both rows should be in returned.

* If you would like to repeat this testing contact me and I will provide you with the SQL scripts used to create the test data above.

Creating the expected test datasets using the ExportDBDataAsXML tool
Next the expected results test data XML files are created using ExportDBDataAsXML provided with DBTestUnit. Four tests will be written – therefore 4 expected test result XML files are created.

The four XML files created are called the following:

TestData_uspGetCurrentEmployeesForDepartment_ExcludeCompanyLeavers
TestData_uspGetCurrentEmployeesForDepartment_ExcludeDeptLeavers
TestData_uspGetCurrentEmployeesForDepartment_ExcludeFutureJoiners
TestData_uspGetCurrentEmployeesForDepartment_IncludeFutureLeavers

The following is added to the XML export tool config. The SQL query used is similar to that used in the previous blog but appropriate filters. To save space the full query is included with the first one – the others have a place holder of ‘[SAME SQL AS ABOVE]’.

 <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_ExcludeCompanyLeavers</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
      SELECT e.BusinessEntityID
      , p.FirstName
      , p.LastName
      , e.JobTitle
      , ed.StartDate
      , ed.EndDate
      FROM HumanResources.EmployeeDepartmentHistory as ed
      INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
      INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
      WHERE ed.DepartmentID = 17
      AND e.CurrentFlag=1
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_ExcludeDeptLeavers</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
	[SAME SQL AS ABOVE]
      WHERE d.DepartmentID = 18
      AND (ed.EndDate IS NULL OR ed.EndDate = GETDATE())
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_ExcludeFutureJoiners</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
	[SAME SQL AS ABOVE]
      WHERE d.DepartmentID = 19
      AND ed.StartDate &lt; GETDATE()
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_IncludeFutureLeavers</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
	[SAME SQL AS ABOVE]
      WHERE d.DepartmentID = 20
    </SQLStatement>
  </SQLObject>

When run, ExportDBDataAsXML will create the 4 XML files, containing the expected test results, and output them to a specified directory.

Once created they should be visibly inspected to ensure that they contain the expected data.

The following show the contents of the XML file containing the expected data set to be returned by the sproc for the test scenario: ‘Exclude company leavers’ – DepartmentID=17

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>20779</BusinessEntityID>
    <FirstName>Joe</FirstName>
    <LastName>Doe</LastName>
    <JobTitle>TestJobTitle</JobTitle>
    <StartDate>2001-12-12T00:00:00+00:00</StartDate>
  </Table>
</NewDataSet>

Writing the DBTestUnit tests
4 new ‘RowTests’ are added to the existing test class – ‘uspGetCurrentEmployeesForDepartment’ – created in the previous blog.

As seen in the sample code below each has the name of the XML file with the expected/required results and the DepartmentId that will be used in the sproc parameter.

using System;
using MbUnit.Framework;
using DBTestUnit.Util;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Functional
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment
    {
        string dbInstance = "AdventureWorks";
        string sprocTestFileDir = AppSettings.DirSprocExpectedResults();

        public uspGetCurrentEmployeesForDepartment() { }

        [RowTest]
        [Row("Dept2", "2")]
        [Row("ExcludeCompanyLeavers", "17")]
        [Row("ExcludeDeptLeavers", "18")]
        [Row("ExcludeFutureJoiners", "19")]
        [Row("IncludeFutureLeavers", "20")]
        public void T01_CheckRowsFromExec(string fileNameSuffix, string sqlParam)
        {
            string fileName = "TestData_uspGetCurrentEmployeesForDepartment_" + fileNameSuffix;
            string sqlText = "EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=" + sqlParam;
            bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, fileName, dbInstance, sqlText);
            Assert.IsTrue(areTheSame, "The dataset created by the SQL run in this test is not the same as that from the file created previously.");
        }
    }
}

Initially running the tests
The image below shows the MBUnit GUI when these tests are run.

1 test passes*. The current implementation meets the requirement to ‘Include future department leavers’.

3 of the tests fail. For example, it fails for ‘Exclude company leavers’.

Running the following SQL statement shows why:

EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=17

As can be seen in the image below – the data set returned still includes the test row added for the person that has left the company.

* Ideally a ‘failing test’ should be written first. To make it easier to write this blog I have written and implemented all 4 tests in one go. In reality they would often be carried out one by one. This test is included to get around the fact of ‘EndDate’ being nullable. As will be shown below, to implement ‘Exclude future department joiners’ the following query filter is used:

ed.EndDate IS NULL

When this is added to the sproc, without including:

OR ed.EndDate >= GETDATE())

this test would then fail.

Making all the tests pass
The existing sproc can be amended using a script similar to that shown below:

IF EXISTS(SELECT ROUTINE_NAME 
		FROM INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_TYPE = 'PROCEDURE'
		AND ROUTINE_SCHEMA + '.' + ROUTINE_NAME = 'HumanResources.uspGetCurrentEmployeesForDepartment')
	DROP PROC HumanResources.uspGetCurrentEmployeesForDepartment
GO

CREATE PROC HumanResources.uspGetCurrentEmployeesForDepartment
	@departmentID smallint
AS

SET NOCOUNT ON

SELECT e.BusinessEntityID
, p.FirstName
, p.LastName
, e.JobTitle
, ed.StartDate
, ed.EndDate
FROM HumanResources.EmployeeDepartmentHistory as ed
INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
WHERE ed.DepartmentID = @departmentID
AND e.CurrentFlag=1
AND (ed.EndDate IS NULL OR ed.EndDate >= GETDATE())
AND ed.StartDate <= GETDATE()

GO

The following filters have been added:

  • ‘Exclude company leavers’
    e.CurrentFlag=1 
  • ‘Exclude future department joiners’ and ‘Include future department leavers’
    (ed.EndDate IS NULL OR ed.EndDate >= GETDATE())
  • ‘Exclude future department joiners’
    ed.StartDate <= GETDATE()

When the tests are run again they now all pass as shown in the image below:

Now if the following SQL statement is run again:

EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=17

As can be seen in the image below, the sproc now only returns one row – no longer returning a row for person who has left the company.

Summary
This blog has looked at how DBTestUnit can be used to explicitly test query filters when testing the data outputted by a sproc.

At first this type of testing might seem cumbersome and perhaps, to some, even an overkill:

But I find this approach offers a number of advantages including:

  • As tests have to be written first it tends to ensure that there is a focus on the exact requirements of the sproc.
  • The tests act as documentation. This is really useful if future changes are required.

What next
The next blog in the series will take a look at the final part from Database unit testing patterns – how to take a TDD approach for testing the permissions/security of a sproc.


Unit testing ‘Select’ stored procedures – part 3

2011/04/02

Step 3 – unit testing the functionality of the a new sproc
This is the third blog in a series looking at how to take a TDD approach when creating new sprocs.

It will look at the third step outlined in – Database unit testing patterns – how to test the functionality of a sproc. In this case, how to ensure that the data outputted meets the requirements.

* It is assumed that you have read parts 1 and 2 in this series.

Overview of the overall process

  • Identify the requirements – the data that should be returned by the sproc.
  • Ensure that appropriate test data has been set up.
  • Use the ExportDBDataAsXML tool to create XML files that contain the datasets that should be returned by the sproc.
  • Write the data comparison unit tests using DBTestUnit. When first run these will fail.
  • Write the implementation SQL script for the sproc.
  • Run the tests again. They will pass if the data returned by the sproc matches the ‘expected test data’ in the XML files.

Why test the data outputted by the sproc
The data outputted by a sproc is part of the data contract – the ‘DB API’ – offered by the database to its client. When changes/refactorings are made to the database, having a set of automated unit tests can make it easier to ensure that this DB API is maintained.

Also, from a personal viewpoint, I find that writing tests first – ie defining the requirements – means the implementation is more likely to meet the requirements.

Note
The purpose of this blog is to give an initial introduction into taking a TDD approach when carrying out data comparison type unit testing.

The implementation produced in this blog will not have all of the required query filters to ensure that only ‘current employees are returned’. The next blog in this series will show how to create more ‘granular’ tests – with each query filter explicitly unit tested.

Background scenario
The high level requirement can be outlined as:

“For a given department the sproc should return the details of current employees in that department.”

To test the data that will be outputted there are two areas that need defining:

a) The data elements that the sproc should return. This along with the properties tested in the previous blog effectively make up the ‘DB API’ – the contract offered by the sproc to clients of it.

b) The logic within the sproc that ensures only ‘current employees’ of a department are returned ie the query filters.

The following data elements are required to be returned by the sproc*:

BusinessEntityID (unique ID of a person) FirstName, LastName, JobTitle, StartDate and EndDate (of working for a particular department).

* In reality to get to this point can involve a large degree of analysis/collabaration between different people.

Ensuring that test data exists
The next step is to ensure that appropriate test data exists.

The list of data elements above can be sourced from the existing AdventureWorks database using the following tables:

Person.Person, HumanResources.Employee and HumanResources.EmployeeDepartmentHistory

The following SQL statement can be used to return the data that is expected for the department with a DepartmentID = 2

SELECT e.BusinessEntityID
, p.FirstName
, p.LastName
, e.JobTitle
, ed.StartDate
, ed.EndDate
FROM HumanResources.EmployeeDepartmentHistory as ed
	INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
	INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
WHERE ed.DepartmentID = 2

The image below shows the data that is returned when this query is run against the database:

Unit testing data outputs from sprocs - expected data

At this point, the data currently in the sample database is appropriate for carrying out initial testing

The next blog will look at actually creating data to test specific query filters.

Creating the expected test datasets
The ‘expected test data’, that the sproc should return, will be stored in XML/XSD files.

The ExportDBDataAsXML tool can be used to create these. More detail on how to set up/configure this tool can be found at – Using DBTestUnit to test data outputs from SQL objects.

For testing the new sproc the following is added to the ExportDBDataAsXML config file.

<!--********************-->
<!--SECTION FOR SPROCS-->
<!--********************-->
<Sprocs>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_Dept2</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
      SELECT e.BusinessEntityID, p.FirstName, p.LastName, e.JobTitle, ed.StartDate, ed.EndDate
      FROM HumanResources.EmployeeDepartmentHistory as ed
      INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
      INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
      WHERE ed.DepartmentID = 2
    </SQLStatement>
  </SQLObject>

When the tool is run it queries the database using the SQL set in the ‘SQLStatement’ element. The output is placed in XML/XSD files – with the names set by the ‘FileName’ element (the directory it is created in is configured in another section.)

The data outputted is shown below for the XML and XSD files respectively:

TestData_uspGetCurrentEmployeesForDepartment_Dept2.xml

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>4</BusinessEntityID>
    <FirstName>Rob</FirstName>
    <LastName>Walters</LastName>
    <JobTitle>Senior Tool Designer</JobTitle>
    <StartDate>2004-07-01T00:00:00+01:00</StartDate>
  </Table>
  <Table>
    <BusinessEntityID>11</BusinessEntityID>
    <FirstName>Ovidiu</FirstName>
    <LastName>Cracium</LastName>
    <JobTitle>Senior Tool Designer</JobTitle>
    <StartDate>2005-01-05T00:00:00+00:00</StartDate>
  </Table>
  <Table>
    <BusinessEntityID>12</BusinessEntityID>
    <FirstName>Thierry</FirstName>
    <LastName>D'Hers</LastName>
    <JobTitle>Tool Designer</JobTitle>
    <StartDate>2002-01-11T00:00:00+00:00</StartDate>
  </Table>
  <Table>
    <BusinessEntityID>13</BusinessEntityID>
    <FirstName>Janice</FirstName>
    <LastName>Galvin</LastName>
    <JobTitle>Tool Designer</JobTitle>
    <StartDate>2005-01-23T00:00:00+00:00</StartDate>
  </Table>
</NewDataSet>

TestData_uspGetCurrentEmployeesForDepartment_Dept2.xsd

<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Table">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="BusinessEntityID" type="xs:int" minOccurs="0" />
              <xs:element name="FirstName" type="xs:string" minOccurs="0" />
              <xs:element name="LastName" type="xs:string" minOccurs="0" />
              <xs:element name="JobTitle" type="xs:string" minOccurs="0" />
              <xs:element name="StartDate" type="xs:dateTime" minOccurs="0" />
              <xs:element name="EndDate" type="xs:dateTime" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

Once these files have been created they should be checked to ensure they are returning the expected data.

Writing a test that defines the requirement/expectations
The next step is to write the data comparison unit tests.

As mentioned in previous blogs DBTestUnit provides a number of sample C# test templates. Therefore, the easiest way to get started is to copy one and change appropriately.

The sample code below shows a data comparison test for the new sproc:

using System;
using MbUnit.Framework;
using DBTestUnit.Util;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Functional
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment
    {
        string dbInstance = "AdventureWorks";
        string sprocTestFileDir = AppSettings.DirSprocExpectedResults();

        public uspGetCurrentEmployeesForDepartment() { }

        [RowTest]
        [Row("TestData_uspGetCurrentEmployeesForDepartment_Dept2", "EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2")]
        public void T01_CheckRowsFromExec(string fileName, string sqlText)
        {
            bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, fileName, dbInstance, sqlText);
            Assert.IsTrue(areTheSame, "The data returned by the SQL text run in this test is not the same as that in the XML file.");
        }
    }
}

What happens when the tests are run?
When the test is first run it will fail.

As executing the following SQL statement:

‘EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2’

will not return any data. Obviously this will not be the same when compared to the data contained in the expected test data XML/XSD files created above.

The image below shows the output from the MBUnit console when this test is run

Unit testing data outputs from sprocs - failing tests

Making the test pass
Next just enough SQL is written to ensure that the test passes.

A script – as shown below – is created and run against the database.

IF EXISTS(SELECT ROUTINE_NAME 
			FROM INFORMATION_SCHEMA.ROUTINES
			WHERE ROUTINE_TYPE = 'PROCEDURE'
			AND ROUTINE_SCHEMA + '.' + ROUTINE_NAME = 'HumanResources.uspGetCurrentEmployeesForDepartment')
	DROP PROC HumanResources.uspGetCurrentEmployeesForDepartment
GO

CREATE PROC HumanResources.uspGetCurrentEmployeesForDepartment
	@departmentID smallint
AS

SET NOCOUNT ON

SELECT e.BusinessEntityID
, p.FirstName
, p.LastName
, e.JobTitle
, ed.StartDate
, ed.EndDate
FROM HumanResources.EmployeeDepartmentHistory as ed
	INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
	INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
WHERE ed.DepartmentID = @departmentID

GO

When the test is run again – it will now pass.

Now, the data returned by the sproc is the same as that contained in the expected test data files.

This is shown in image below:

Unit testing data outputs from sprocs - passing tests

How does this work?
There are two parts to a data comparison unit test as shown in the sample code below:

        [RowTest]
        [Row("TestData_uspGetCurrentEmployeesForDepartment_Dept2", "EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2")]
        public void T01_CheckRowsFromExec(string fileName, string sqlText)
        {
            bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, fileName, dbInstance, sqlText);
            Assert.IsTrue(areTheSame, "The data returned by the SQL text run in this test is not the same as that in the XML file.");
        }

1. At the top – the ‘Row’ part.
The names of the expected data test file and the actual SQL statement that will be executed are set.

The XML/XSD files named:

‘TestData_uspGetCurrentEmployeesForDepartment_Dept2’

will be compared to the data returned by executing:

‘EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=2’

2. The DBTestUnit test method that will carry out the data comparison.
The values set above are passed to the test method – ‘DataSetComparer.Compare’ (part of the DBTestUnit.Util namespace).

This method ‘grabs’ the expected data from the XML/XSD files and transforms it into datasets and then compares this to the dataset returned by running the SQL statement against the database.

If the datasets are the same the test method will return ‘true’ and the test will pass.

If there are any differences it will return ‘false’ and the test will fail.

What happens if ‘unexpected’ changes are made?
There are two types of changes which these types of tests will identify.

1. Schema changes of the dataset returned. If this is changed in anyway eg column names, ordinal position, adding/removing a column and data types*.

2. Logic changes that cause the number of rows returned to change. For a given input there is an expected set of rows to be returned. If, for example, a change is made to a query filter that effects the rows returned – then this type of test will quickly identify this.

* Due to the method currently used by DBTestUnit to compare datasets actually returned by the sproc to those which are expected (XML/XSD comparison) – some types of schema changes on the dataset returned by sproc will not be detected. Looking at the XSD – TestData_uspGetCurrentEmployeesForDepartment_Dept2.xsd shown above – if the underlying SQL column for ‘LastName’ is changed, for example, from nvarchar(50) to nvarchar(40) this type of test will not necessarily identify this. I hope to improve on this in a future version.

Acknowledgements
The idea behind how to carry out this data comparison and the implementation used within the DBTestUnit method – DataSetComparer.Compare – was based on an article written by Alex Kuznetsov and Alex Styler Close those Loopholes – Testing Stored Procedures.

Future release
The next release of DBTestUnit – post version – 0.4.0.428 – will use an updated version of MBUnit (from v2.4 to v3.2).

The latest version of MBUnit makes it easier to work with XML – see MbUnit 3.2 makes it easier to work with Xml

For example it has an inbuilt test method – Assert.Xml.AreEqual. The advantage of using this is the fact that if the assert fails it will display any differences between two XML files. This can save a lot of time troubleshooting failing tests. Writing a test will be very similar to the sample code shown above.

Note the existing method DataSetComparer.Compare will continue to be supported.

What next
This blog has given a quick overview on how to take a TDD approach when testing the expected data that should be outputted by a new sproc.

The next blog in this series will extend this and take a more detailed look at how to explicitly test each ‘query filter’.