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><IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey></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><IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey></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.
Posted by Michael Baylon 
DBTestUnit 