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:
- Unit testing insert, delete and update stored procedures – intro
- Unit testing update stored procedures – part 1
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.
DBTestUnit 