Unit testing update stored procedures – part 1

2011/06/29

Introduction
This is the first in a series of blogs that will look at unit testing an ‘update’ sproc using taking a TDD approach.

Testing that the sproc exists and that it has it’s expected properties will be covered – following steps 1 and 2 as per the Database unit testing patterns.

What are the requirements of the sproc
HumanResources.uspUpdateEmployeePersonalInfo from the AdventureWorks2008R2 database will be used as an example.

It updates the HumanResources.Employee table with the values specified in the input parameters for a specified Employee – using the primary key BusinessEntityID.

The SQL script to create it is shown below:


CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
@BusinessEntityID [int], 
@NationalIDNumber [nvarchar](15), 
@BirthDate [datetime], 
@MaritalStatus [nchar](1), 
@Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
UPDATE [HumanResources].[Employee] 
SET [NationalIDNumber] = @NationalIDNumber 
,[BirthDate] = @BirthDate 
,[MaritalStatus] = @MaritalStatus 
,[Gender] = @Gender 
WHERE [BusinessEntityID] = @BusinessEntityID;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspLogError];
END CATCH;
END;

This sproc will be dropped from the database and recreated from scratch – but taking a TDD type approach.

The ‘end game’ is to recreate the sproc as above (with a couple of minor changes that I will mention along the way).

The first thing is to run the following script to delete it from the test database:

DROP PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo

Step 1 Testing that the sproc exists
A similar approach to that in Unit testing ‘Select’ stored procedures – part 1 is used.

The unit test class – ..\Tests\DatabaseTest.cs – tests for the existence of all expected SQL objects that should be in the database.

As the sproc was previously present its name is already in the list of expected sprocs (‘expectedSprocList’ and the ‘expectedSprocCount’ is already 11).

When this tests are run those related to sproc counts fail as the sproc was deleted so the count is down to 10.

To get the test to pass the following script is run:

CREATE PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo
AS
BEGIN
SET NOCOUNT ON;
END
 

The tests now pass.

Step 2 Testing it’s properties
A similar approach to that in Unit testing ‘Select’ stored procedures – part 2 is be used.

Take a copy of one of the sample unit test classes included in the DBTestUnit download and rename it to uspUpdateEmployeePersonalInfo_Schema.

Update it appropriately so that the unit test class looks like the following:


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

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Schema
{
    [TestFixture]
    public class uspUpdateEmployeePersonalInfo : SprocTestBase
    {
        public uspUpdateEmployeePersonalInfo()
        {
            dbInstance = "AdventureWorks";
            schema = "HumanResources";
            sprocName = "uspUpdateEmployeePersonalInfo";
            sproc = new Sproc(dbInstance, schema, sprocName);
            expectedInputCount = 5;
            expectedInputList = "@BusinessEntityID,@NationalIDNumber,@BirthDate,@MaritalStatus,@Gender";
            expectedOutputCount = 0;
            expectedOutputList = "";
        }
        [RowTest]
        [Row("@BusinessEntityID ", "1,IN,int,N/A")]
        [Row("@NationalIDNumber ", "2,IN,nvarchar,15")]
        [Row("@BirthDate", "3,IN,date,N/A")]
        [Row("@MaritalStatus", "4,IN,nchar,1")]
        [Row("@Gender", "5,IN,nchar,1")]
        public void T05_ParameterProperties(string paramName, string expectedParameterProperties)
        {
            string parameterProperties = sproc.ParameterProperties(paramName);
            Assert.AreEqual(expectedParameterProperties, parameterProperties, "Param properties are not as expected");
        }

        [RowTest]
        [Row("@BusinessEntityID", "HumanResources.Employee", "BusinessEntityID")]
        [Row("@NationalIDNumber", "HumanResources.Employee", "NationalIDNumber")]
        [Row("@BirthDate", "HumanResources.Employee", "BirthDate")]
        [Row("@MaritalStatus", "HumanResources.Employee", "MaritalStatus")]
        [Row("@Gender", "HumanResources.Employee", "Gender")]
        public void T06_ParameterIsTheSameDataTypeAndLength(string paramName, string tableName, string columnName)
        {
            bool areTheSame = sproc.ParameterIsSameAsColumn(paramName, tableName, columnName);
            Assert.IsTrue(areTheSame, "Parameter and column are not the same");
        }
    }
}

This will test that the sproc’s parameters are as expected – correct number of parameters and properties.

It will also check that the parameters have the same data types as the base table columns that they will update. It is very easy to make a mistake and not have the parameters exactly match the columns (see this blog for more detail).

* The paramater ‘@BirthDate’ in the original sproc – see SQL script above – has a data type of ‘datetime’ whereas the underlying base table column HumanResources.Employee has a type of ‘date’. The sproc created in this series of blogs will have ‘@BirthDate’ as ‘date’.

Compile and run the tests. They will fail as the sproc does not currently have any parameters.

To make it pass the tests the following script is run:


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;


END
 

Run the test again and they now pass as the sproc meets the expectations/requirements as per the tests.

What next
Steps 1 and 2 as per the Database unit testing patterns have been carried out and an initial sproc has been created.

The next blog in this series will look at Step 3 – testing the functionality of the sproc – ensuring that it updates the base table as expected.


Unit testing insert, delete and update stored procedures – intro

2011/06/27

Up to now the previous blogs on unit testing sprocs have focused on those that output data – ‘Select’ sprocs. Over the next few weeks I intend to write a series of blogs that will look at testing sprocs that carry out other types of data manipulation.

The steps outlined in Database unit testing patterns will be followed for testing delete, insert and update sprocs. But as steps 1,2 and 4 are effectively the same as outlined previously, the focus will be on Step 3 – testing the functionality of the sproc.

Does the sproc delete/insert/update as expected?

Over the series a functional test pattern will emerge for testing all three types of sprocs.

This can be outlined as:

1. Test initial state is as expected
For example, if testing an update sproc ensure that the row(s) to be updated has the expected initial values (ie it hasn’t already been updated by a previous test and not ‘rolled back’).

2. Run the sproc

3. Test post state is as expected
For example, after running an update sproc ensure that the row(s) has been updated correctly AND it hasn’t inadvertantly effected any other rows.

I will give examples of two different approaches that can be used to do the steps outlined above:

A. Using the ExportDBDataAsXML tool to create expected pre and post state XML files that can that can be compared to ‘actuals’.

B. Using ‘ad hoc’ SQL commands to test checksums, row counts and values in rows for both pre and post states.

The series will start with a relatively straight forward example of an update sproc – taken from Microsoft’s AdventureWorks2008R2 – which updates a single table. This will then be extended to look at how to handle more complex scenarios, such as where 2 or more base tables are involved.


Using ad hoc SQL commands in DBTestUnit tests

2011/06/20

When writing database tests you will find that you need to excute a SQL statement as part of the test.

For example, you might want to check the number of rows in a table before and after running an ‘insert’ sproc or to execute the sproc from your C# tests.

The DBTestUnit.Util.DALHelper class provides three methods that allow SQL commands to be run against your database.

These are:

  • ExecuteScalar
  • ExecuteDataSet
  • Execute

All take two strings as parameters – the dbInstance (defines which db to run the SQL command against) and the SQL text to run.

Which one to use depends on the expected output:

  • No value to be returned.

    For example to carry out an insert of a test record into a table:

    string sqlText = "INSERT INTO HumanResources.Department (Name, GroupName) VALUES ('TestDeptName', 'TestGroupName')";
    DBTestUnit.Util.DALHelper.Execute(dbInstance, sqlText);
    
  • Scalar value to be returned.

    For example, to get the row count of a table:

    string sqlText = "SELECT COUNT(*) FROM HumanResources.Department";
    int rowCount = System.Convert.ToInt32(DALHelper.ExecuteScalar(dbInstance, sqlText));
    
  • Dataset to be returned
    sqlText = "SELECT * FROM  HumanResources.Department";
    DataSet ds =DBTestUnit.Util.DALHelper.ExecuteDataSet(dbInstance, sqlText);
    


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.