Configuring your DBTestUnit test project

2011/06/14

There are two places that need to be configured to allow tests to run:

1. The test dll config file.
2. The ‘dbInstance’ property set in the test classes.

This blog will explain how to config each of these.

Test dll config file
If you have downloaded DBTestUnit and followed the instructions outlined here – you will find the test dll config file – AdventureWorks.DatabaseTest.dll.config – in the following dir:

C:\Projects\AdventureWorksBlog\src\AdventureWorksDatabaseTest\bin\Debug\

This contains the connections settings for the database(s) that will be tested.

The following text shows a sample database connection for a MS SQL server from this file.

<connectionStrings>
<add name="AdventureWorks" connectionString="Data Source=serverName;Initial Catalog=AdventureWorks;Integrated Security=True;Application Name=AdventureWorksUnitTesting" providerName="System.Data.SqlClient"/>
</connectionStrings>

There are two parts to configuring a MS SQL server database

A) The element ‘name’ shown above as – name=”AdventureWorks”. This is referenced within the test classes.
B) The element ‘connectionString’. This is a standard SQL server connection string – consisting of:

  • ‘Data Source=serverName’ – the SQL server to connect to.
  • ‘Initial Catalog=AdventureWorks’ – the database to test.
  • ‘Integrated Security=True’ – use trusted security ie uses the account that the tests are being run under.

Note DBTestUnit requires read access to information_schema and system views. Therefore, the account being used will need appropriate access.

Test class
The sample code, shown below, is for testing a sproc in the AdventureWorks db.

using System;
using MbUnit.Framework;
using DBTestUnit.InfoSchema;
using DBTestUnit.UnitTestBaseClass.MSSQL;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Schema
{
    [TestFixture]
    public class uspGetManagerEmployees : SprocTestBase
    {
        public uspGetManagerEmployees()
        {
            dbInstance = "AdventureWorks";

As can be seen the ‘dbInstance’ property is set to ‘AdventureWorks’. This test class would use the connection settings in the config file set by the element ‘name’ which has a value of ‘AdventureWorks’.

Example
Imagine the scenario where you want to carry out both unit and deployment tests using the same Visual Studio project ie only having one test dll and config file. But, you want to use different SQL servers/databases for each type of test – with settings as outlined below:

Type SQL server Database dbInstance
Unit test SQLServer01 TestDb MyUnitTests
Deployment test SQLServer02 DeploymentDb MyDeploymentTests

To do this the following would be added to the test dll config file.

<connectionStrings>
<add name="MyUnitTests" connectionString="Data Source=SQLServer01;Initial Catalog=TestDb;Integrated Security=True;Application Name=UnitTesting" providerName="System.Data.SqlClient"/>

<add name="MyDeploymentTests" connectionString="Data Source=SQLServer02;Initial Catalog=DeploymentDb;Integrated Security=True;Application Name=DeploymentTesting" providerName="System.Data.SqlClient"/>
</connectionStrings>

Each type of test class would also need to reference the appropriate configuration settings – ‘dbInstance’ would be set as follows in each type of test class.

Unit test

    [TestFixture]
    public class someUnitTest
    {
        public someUnitTest()
        {
            dbInstance = "MyUnitTests";

Deployment test

    [TestFixture]
    public class someDeploymentTest
    {
        public someDeploymentTest()
        {
            dbInstance = "MyDeploymentTests";


Running DBTestUnit tests using MBUnit

2011/06/08

The following gives a brief overview on how to use MBUnit (v 2.41.232.0) using the GUI or console.

This version of MBUnit is the default testing framework up to version 0.4.0.428 of DBTestUnit.

It assumes MBUnit has been installed to C:\Program Files\MbUnit\.

GUI
1. Open the GUI via: C:\Program Files\MbUnit\MbUnit.GUI.exe

2. Click on ‘Assemblies – Add Assemblies’

Browse to location of the the database unit test dll to be tested.

eg for AdventureWorks – the example database used in previous blogs – browse to:

C:\Projects\AdventureWorks\src\AdventureWorksDatabaseTest\bin\Debug\
AdventureWorks.DatabaseTest.dll

3. The following image shows what the GUI looks like:


To run the tests simply click the ‘Run’ button.

Individual tests or groups of tests can be run by selecting various nodes on the hierarchy on the left hand side.
Console
1. The console can be run via: C:\Program Files\MbUnit\MbUnit.Cons.exe.

2. The following sample bat script could be used to run all the sample AdventureWorks database unit tests.


SET testDll=C:\Projects\AdventureWorks\src\AdventureWorksDatabaseTest\bin\Debug\AdventureWorks.DatabaseTest.dll

cd C:\Program Files\MbUnit\

MbUnit.Cons.exe %testDll% /v

3. The following examples show a number of different switches that can be used to limit the set of tests to run.

/fc:CoreTests
Only run test fixtures categorised as ‘CoreTests’

/fn:AdventureWorks.DatabaseTest.Tests.Sprocs.Schema
Only run test fixtures in this namespace.

/ft:AdventureWorks.DatabaseTest.Tests.Sprocs.Schema.uspGetManagerEmployees
Only run this individual test fixture.


Running your first tests

2009/11/18

Last modified: 2011-02-28

This blog is the second in a two part series.

It will look at:

  • running the some simple tests against the database
  • using the provided SQL helper scripts to help get the properties for an existing database.

It assumes that you have read the first blog in the series – initial set up and configuration.

Example test to be run

The DBTestUnit download comes with a number of sample test files including:

DIR\src\AdventureWorksDatabaseTest\SQLServer.cs

(Note ‘DIR’ is used as a token for ‘C:\Projects\AdventureWorks\’ – the example project set up in initial set up and configuration).

This allows tests to be run against the SQL server that hosts the database – to ensure it has the expected properties.

An example is shown below:

using System;
using MbUnit.Framework;
using DBTestUnit.UnitTestBaseClass.MSSQL;
using DBTest = DBTestUnit.InfoSchema;

namespace DBTemplate.DatabaseTest.Tests
{
    [TestFixture]
    public class SQLServer : SQLServerTestBase
    {
        public SQLServer()
        {
            dbInstance = "AdventureWorks";
            sqlServer = new  DBTest.SQLServer(dbInstance);
            expectedServerCollation = "Latin1_General_CI_AS";
            expectedServerVersion = "10.50.1600.1";
            expectedServerProductLevel = "RTM";
            expectedServerEdition = "Developer Edition";
        }
    }
}

A number of things are worth noting:

  • This is for MS SQL server. The test inherits from SQLServerTestBase part of the DBTestUnit.UnitTestBaseClass.MSSQL namespace. This would need to be changed if testing Oracle or MySQL.
  • The expected values eg expectedServerVersion – will need to be changed appropriately.

This will be used as an example as it is a relatively straight forward set of tests.

How to run the tests
So assuming that everything has been set up and configured correctly how do we run the tests?

1. Open up the VS solution.

If you haven’t already done so open the solution and build/compile the database test project.

The output of this will be a test dll – for example:

DIR\src\AdventureWorksDatabaseTest\bin\Debug
\AdventureWorks.DatabaseTest.dll

2. Open the MBUnit UI

If you need to install either get the latest version from the MBUnit web site or install if from the DBTestUnit download

DIR\tools\mbunit2.41.232.0

In the UI go to the menu ‘Assemblies’ – ‘Add assemblies’

Navigate to and attach the db test dll created in step 1.

Run the SQLServer tests.

If the expected properties in the tests are correct then the tests will pass.

3. What do you do if some of the test fail
This will mean that the SQL server that hosts the AdventureWorks – does not have the properties outlined in test code above.

Therefore the sample test code provided needs to be updated with the correct SQL server properties.

(Note this blog is using the scenario where you are starting to carry out database testing for an existing/legacy database.)

The DBTestUnit download provides a number of SQL helper files that will provide information for an existing database.

These can be found in:

DIR\misc\AdventureWorksDatabase\SQL\DatabaseTestingHelperScripts\

4. Using the SQL helper scripts

The script 001_SQLServerSettings.sql is used in this case.

A part of the script is shown below:

PRINT '********************************'
PRINT '********************************'
PRINT 'SERVER PROPERTIES'
PRINT 'ProductVersion: ' + convert(varchar,SERVERPROPERTY ('ProductVersion'))
PRINT 'ProductLevel: ' + convert(varchar,SERVERPROPERTY ('ProductLevel'))
PRINT 'Edition: ' + convert(varchar,SERVERPROPERTY ('Edition'))
PRINT 'ServerCollation: ' + convert(varchar,SERVERPROPERTY ('Collation'))

The values returned when this script is run can be placed in the C# test shown above.

It can then be recompiled and the tests run again in MBUnit.

If the values are correct the tests will all pass.

Round up
And that’s it.

The first blog showed how to configure/set up DBTestUnit, this blog has taking this on and shown how to run some initial tests.


Initial set up and configuration of DBTestUnit

2009/11/17

Last modified: 2011-06-08

Overview
This is the first in a two part series on how to set up the Visual Studio (VS) template – that is included in the DBTestUnit download.

The second will show how to run tests.

If you already have an existing solution/project and want to start using DBTestUnit – a section is included at the end to cover this scenario.

Screencast
The following short screencast can be used with the notes below to help set up and configure.

(Screencast was created in Aug 2010)

Steps
1. Download the latest version of the DBTestUnit

2. Extract files to where you want to place your database testing solution/project.

In the example below it has been extracted to C:\Projects\ :

DBTemplate default directory structure

DBTemplate default directory structure

3. Rename dirs and files with your database name.

As you can see in the image above by default ‘DBTemplate’ is used as a prefix for many dirs and files.

These should be replaced with the name of the database to be tested.

For this I am going to use the MS sample database AdventureWorks as an example.

Therefore change:

C:\Projects\DBTemplate\ to
C:\Projects\AdventureWorks\

Rather than renaming these manually, a bat file – C:\Project\DBTemplate\tools\DBTemplateSetUp.bat – is provided that can do this.

The first section of this file is shown below.

SET dirRoot=C:\Projects\
SET dirParent=%dirRoot%DBTemplate\
SET dbProjectName=AdventureWorks

Before running the bat file – copy it to another directory eg C:\Projects\ and set appropriate values for ‘dirRoot’ and ‘dbProjectName’.

After renaming the directory structure should be similar to the following:

AdventureWorks directory structure

AdventureWorks directory structure

4. Change the VS solution file

Open – C:\Project\AdventureWorks\src\AdventureWorks.sln

Replace ‘DBTemplate’ with ‘AdventureWorks’

5. Change the the db test project file

Open – C:\Projects\AdventureWorks\src\AdventureWorksDatabaseTest\
AdventureWorks.DatabaseTest.csproj

Replace ‘DBTemplate’ with ‘AdventureWorks’

6. Start VS

Open the solution.

The structure should look similar to the following:

AdventureWorks Visual Studio directory structure

AdventureWorks Visual Studio directory structure

A number of sample unit tests and other files are including the in solution. These will all have the text ‘DBTemplate’ in their namespaces.

Therefore, do a solution wide ‘find and replace’ changing ‘DBTemplate’ to ‘AdventureWorks’

7. Set up the config file to connect to the database to test

If you open up any of the sample tests provided you will see that they all have a variable – dbInstance – which by default has the value ‘AdventureWorks’.

This can be seen at the bottom of the sample code provided below:

using System;
using MbUnit.Framework;
using DBTestUnit.InfoSchema;
using DBTestUnit.UnitTestBaseClass.MSSQL;

namespace DBTemplate.DatabaseTest.Tests.Tables.Schema
{
    [TestFixture]
    public class SalesOrderHeader : TableTestBase
    {
        public SalesOrderHeader()
        {
            dbInstance = "AdventureWorks";

This is used to provide connection settings to the database to be tested and should be set in the following app config file:

..\src\AdventureWorksDatabaseTest\bin\Debug\
AdventureWorks.DatabaseTest.dll.config

Open this config file.

Go to the connectionStrings section.

The following should be included by default.

<connectionStrings>
<!--MS SQL dbInstances-->
<add name="AdventureWorks" connectionString="Data Source=serverName;Initial Catalog=AdventureWorks;Integrated Security=True;Application Name=AdventureWorksUnitTesting" 
			 providerName="System.Data.SqlClient"/>

The ‘name=”AdventureWorks”‘ should correlate with the value set in the variable ‘dbInstance’ as show in the test code above.

The connectionString properties need to be changed appropriately eg Data Source=serverName

Once the config file has been updated – that’s it all set up – ready to start database testing.

What do I do if I have an existing project?
If you an existing project and just want to start using the DBTestUnit.dll for testing then it is a bit easier to set up.

Download the latest version of the DBTestUnit as per step 1.

In your existing test project reference the latest version of DBTestUnit.dll. You will also need to reference the MS enterprise libraries provided in the download.

Then amend your existing config file appropriately so that tests can connect to the database.

Round up
This blog shown how to set up DBTestUnit – the second will look at running tests.