Version – 0.3.2.333 – database testing framework released

Version 0.3.2.333 of the database testing framework has been released and can be downloaded from SourceForge

It includes:

• A new sproc/function test method – ‘ParameterIsSameAsColumn’. This can be used to test that a sproc/function parameter has the same data type/length of an associated table/view column.
• Some of the default SQL objects that were added in the previous version have had some changes made to them. Also, the sproc dbo.p_ExtendedProperty_Insert has been renamed to dbo.p_ExtendedProperty_UpSert – to more accurately reflect what it does.
• A bat file – …DBTemplate\tools\DBTemplateSetUp.bat – that makes it easier/quicker to carry out the initial configuration/set up for a database testing project using the framework.

How to use the ‘ParameterIsSameAsColumn’

An example stored procedure unit test – that includes the new functionality – is shown below.

It uses the sproc – HumanResources.uspUpdateEmployeeHireInfo – from AdventureWorks2008.

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

namespace DBTemplate.DatabaseTest.Tests.Sprocs
{
    [TestFixture]
    public class uspUpdateEmployeeHireInfo : SprocTestBase
    {
        public uspUpdateEmployeeHireInfo()
        {
            dbInstance = "AdventureWorks2008";
            schema = "HumanResources";
            sprocName = "uspUpdateEmployeeHireInfo";
            sproc = new Sproc(dbInstance, schema, sprocName);
            expectedInputCount = 7;
            expectedInputList = "@BusinessEntityID,@JobTitle,@HireDate,@RateChangeDate,@Rate,@PayFrequency,@CurrentFlag";
            expectedOutputCount = 0;
            expectedOutputList = "";
        }

        [RowTest]
        [Row("@BusinessEntityID", "1,IN,int,N/A")]
        [Row("@JobTitle", "2,IN,nvarchar,50")]
        [Row("@HireDate", "3,IN,datetime,N/A")]
        [Row("@RateChangeDate", "4,IN,datetime,N/A")]
        [Row("@Rate", "5,IN,money,N/A")]
        [Row("@PayFrequency", "6,IN,tinyint,N/A")]
        [Row("@CurrentFlag", "7,IN,bit,N/A")]
        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("@JobTitle", "HumanResources.Employee", "JobTitle")]
        [Row("@RateChangeDate", "HumanResources.EmployeePayHistory", "RateChangeDate")]
        [Row("@Rate", "HumanResources.EmployeePayHistory", "Rate")]
        [Row("@PayFrequency", "HumanResources.EmployeePayHistory", "PayFrequency")]
        [Row("@CurrentFlag", "HumanResources.Employee", "CurrentFlag")]
        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");
        }
    }
}

Using the second parameter – @JobTitle – as an example.

It is used to update the base table column – HumanResources.Employee.JobTitle – and therefore should have the same type/length.

The test method – sproc.ParameterProperties(paramName) – in ‘T05_ParameterProperties’ – asserts that the parameter is of type nvarchar(50).

The new test method – sproc.ParameterIsSameAsColumn(paramName, tableName, columnName) is used in ‘T06_ParameterIsTheSameDataTypeAndLength’.

For the parameter @JobTitle it takes in a row test set of values of:

[Row(“@JobTitle”, “HumanResources.Employee”, “JobTitle”)]

This compares @JobTitle to the HumanResources.Employee.JobTitle column and if they are the same – it returns true.

This type of test can be useful in a number of ways:

• When designing sprocs. If a parameter is associated with column – e.g. for inserts, updating – it can help ensure that the correct type/length is set.
• If columns are changed/refactored e.g. the HumanResources.Employee.JobTitle column was changed to nvarchar(25) – then these tests will quickly highlight any sprocs/functions that might be effected by the changes.