Overview
The last blog – 05 Database testing framework – running your first tests – showed how to:
- the final steps in setting up the unit testing visual studio (VS) solution
- running the test SQL helper scripts
- running the first tests against the database
This blog will look at:
- how to auto create a base line set of tests for an existing SQL table
- using a ‘test first’ type approach when making a change to the table
Click to see the complete set of blogs in this series
Steps
1. The AdventureWorks database contains a table – HumanResources.Employee
The image below shows the columns and properties in this table:
Employee table properties
2. To auto create the unit tests for this table
Note from this point ‘DIR’ is a replacement for ‘C:\Projects\AdventureWorks\’.
Open the following file:
DIR\misc\AdventureWorksDatabase\SQL\DatabaseTestingHelperScripts
08_SQLDatabase_ManuallyCreateTests.sql
Run the following sproc – note that the name of the database to test is ‘AdventureWorks’.
EXEC dbo.sp_Sys_UnitTestGenerator_Table
‘AdventureWorks’
, ‘HumanResources’
, ‘Employee’
, ‘AdventureWorks.DatabaseTest.Tests.Tables’
, 10
, 0
3. The output of the above will contain details on the table – columns and their properties, indexes, constraints and trigggers.
Paste it into a new VS C# file AdventureWorksDatabaseTest\Tests\Tables\Employee.cs.
It should look something like the following:
Employee table unit test
Read through the test declarations/expectations in the C# file.
Compare the expectations to those in the image ‘Employee table properties’ in Point 1 above.
They should be the same ie correct number of columns, constraints etc.
4. Rebuild the test project and open MBUnit UI
Run the tests for Tables\Employee.cs. All should pass.
This is the base set of tests for the current table – if something is altered on the table, without updating the expectations, then one or more of the tests should fail.
5. Taking a ‘test first – TDD’ type approach when making a change to a table
We want to add a new column to this table – eg:
PassportNumber – int and nullable
6. First update the test expectations.
For a new column there are three things to change
A) expectedColumnCount = 16; to expectedColumnCount = 17;
B) Include PassportNumber column name in the expectedColumList
expectedColumnList = “PassportNumber,EmployeeID,etc”;
C) Add the expected column properties to test ‘T10_ColumnProperties’
[Row("PassportNumber", "17,YES,int,N/A")]
public void T10_ColumnProperties(string column, string expectedProperties)
This is entered in the format
[Row("ColumnName", "Ordinal number,Nullable - YES/NO,datatype,string length")]
As this is an int – string length is entered as – N/A.
The new ‘expectations’ have now been set.
7. To quickly check what columns are currently in the table – run this sql script
USE AdventureWorks
GO
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA + ‘.’ + TABLE_NAME = ‘HumanResources.Employee’
ORDER BY ORDINAL_POSITION
8. Rebuild the test projects and run tests
As expected there will be a number of test failures as the new columns does not currently exist.
9. Now dev the SQL code so that the unit tests now pass
Add the column to table by running the following SQL:
USE AdventureWorks
GO
ALTER TABLE HumanResources.Employee ADD PassportNumber int NULL
GO
10. Rerun the tests – they should now pass
11. If you don’t want to carry out a ‘test first – TDD’ approach
A) it is possible to make the SQL changes first
B) then update the C# tests for the table using the provided helper sprocs
Both approaches have their benefits – the second especially if you are applying many changes in one go.
But I would recommend taking ‘test first – TDD’ approach.
Writing the tests first ensures that you think about the requirements before writing the SQL change script and applying it
Summary
This blog has shown how to:
- produce a set of base line unit tests for an existing table
- how to use this approach when making a change to a table
What next
The next blog in the series – 07 Database testing framework – auto create tests for all existing tables – as per the title – will look at auto creating unit tests for all tables in an existing database.
Click to see the complete set of blogs in this series
Posted by michaelbaylon 
