Using DBTestUnit to test data outputs from SQL objects


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:

..\DBTemplate\tools\ExportDBDataAsXML\

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.

Config for SELECT and EXEC statements

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.

Config for dir to output 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.

Example output XML file

Click on to enlarge - Example output XML file

Example output XSD 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.

Example table data compare test

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:

SalesOrderHeader_Functional.cs
SalesOrderHeader_Persmissions.cs
SalesOrderHeader_Schema.cs

These are also separated into 3 different namespaces:

DBTemplate.DatabaseTest.Tests.Tables.Functional
DBTemplate.DatabaseTest.Tests.Tables.Permissions
DBTemplate.DatabaseTest.Tests.Tables.Schema

As mentioned above this aid test management and allows flexibility as you can then run different categories of tests based on namespace.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: