Last modified: 2011-01-08
This blog will look at how DBTestUnit can be used to test the data returned by SQL objects such as tables, views and sprocs.
Why test data outputs from SQL objects?
Testing the functionality/behaviour of SQL objects is one of the key areas of database unit testing.
For many SQL objects the primary behaviour is in returning data.
This type of testing provides two key advantages:
- Help when refactoring/changing a schema. For example, if you want to refactor a sproc. You will want to maintain the contract it offers to external applications. This type of test can help ensure that whilst, changing the internals of a sproc, it continues to return the expected dataset.
- Reference data. Most database contains tables that store reference data eg countries, offices, regions, contact types in AdventureWorks.
This can help ensure that different environments contain the same set of reference data and to identify if any ‘unplanned’ changes have been introduced.
Overview of how it works
- A tool – ExportDBDataAsXML – is provided as part of the DBTestUnit download.
- At design time ‘SELECT’ or ‘EXEC’ statements are run against SQL objects with known sets of data in the database.
- ExportDBDataAsXML outputs these as XML/XSD and the files saved.
- These are checked to ensure the data is correct. This are the expected data output.
- Data output tests are written for the appropriate SQL object.
- When the tests are run data is returned for the SQL object and compared the XML file.
- DBTestUnit has a method that can compare an XML file to the dataset returned from a SQL object.
What is the ExportDBDataAsXML.exe?
ExportDBDataAsXML – is provided as part of the DBTestUnit download.
It is in the following dir:
There are 2 key config files:
DBTestUnit.ExportDBDataAsXML.exe.config – contains database connection strings and references the SQLStatement.config file below:
DBTestUnit.ExportDBDataAsXML.SQLStatement.config – contains the SELECT/EXEC statements that are written at design time.
These are used to output the ‘expected’ XML data files. The directory where these should be outputted to can be set here as well.
The image below shows some example statements for SQL objects in the AdventureWorks database.
Note each type of SQL object – table, view, sproc and function has a separate section.
Click on to enlarge - Config for SELECT and EXEC statements
The image below shows the section to config where to output the XML/XSD files to.
Click on to enlarge - Config for dir to output files to
Using ExportDBDataAsXML.exe to create the output files
Before creating the files and running the tests it is important to ensure that the database is in a ‘known state’.
Typically whilst developing a database I will create a set of data load scripts that allow me to put the database in the a known state.
To test a table eg AdventureWorks.Person.ContactType – write the select statement (for a sproc it would be an exec statement).
Enter this into the ‘Tables’ section of the DBTestUnit.ExportDBDataAsXML.SQLStatement.config
Run the exe it will output an XML file and XSD similar to the images below.
Click on to enlarge - Example output XML file
Click on to enlarge - Example output XSD file
Visually check both files to ensure they are returning the data you expect.
To save time and not output all files for all statements in one go – you can put a ‘No’ in the ‘Execute’ section against and individual SQL statement.
This will stop it running. In this way you can just output the XML for the individual SQL object you are testing.
Writing the tests
An example for AdventureWorks.Person.ContactType table is shown below.
Click on to enlarge - Example table data compare test
It is possible to run multiple outputs tests for the same SQL object.
You might do this if you were testing a sproc ie test a number of different input parameters. MBUnit [RowTest] feature can be used to pass in multiple tests parameters.
Each would need to have a separate ‘expected data output’ file created by ExportDBDataAsXML.exe
Once the test has been created – run it. It will query the database and place the data as ‘in memory’ XML.
It will then compare this output to the ‘expected data output’ XML/XSD files.
If they are different – it will fail.
Organising the data output tests
In general there are three types of database tests – schema, functional (data outputs) and permission tests.
To help manage these I tend to separate them into separate test files.
So for example – the SQL table SalesOrderHeader – has three test files:
These are also separated into 3 different namespaces:
As mentioned above this aid test management and allows flexibility as you can then run different categories of tests based on namespace.