A Simple Unit Test Script for SQL Server

As we develop applications, it often makes sense to put some functions in a SQL CLR assembly.  As these assemblies are deployed through test, beta and production environments we need an easy way to ensure the assemblies have been updated correctly and are functioning as designed.  Many things can go wrong—incorrect permissions, deploying the wrong version of an assembly, even assumptions we made in the business logic.  I’m a big fan of RedGate’s SQL Test plugin—it’s an attractive SSMS plugin which organizes and runs tests via an easy to read panel—and the tSQLt (a unit testing framework for SQL Serve) which SQL Test is based on.  My next post will show recreate the testing strategy shown here using SQL Test.

One downside to tSQt is that you have to install a number of stored procedures and functions into your database.  Depending on your choice of database, permissions in your database and willingness to add additional objects to your database which aren’t drectly related to the data, using tSQLt may not be an option.  Although we’re testing a user defined function, we could also test stored procedures and SQLCLR assemblies.  This technique is also not limited to SQL Server—I use this in both SQL Server and VistaDB.  As you build out your application, you may want to consider adding diagnostic pages (or screens) where you can execute these testing procedures should your application start throwing errors.

The function we’ll be testing will be simple—we’ll pass in an nvarchar, and return the same nvarchar with the word “wombat” added to the end.

Start by creating a custom function:

CREATE FUNCTION [AddWombat]
( @phrase NVarChar(50)
)
RETURNS NVarChar
AS
BEGIN
return @phrase + ' wombat'
END

Now it’s time for the test harness.  I’m using VistaDB, creating a stored procedure and using table variables, syntax supported by more recent versions of SQL Server.  You may need to optimize for the database you’re using.  The first table holds our test cases—the test name, the value we want to pass into our function, and the expected result.  We then loop through all the tests, calling the function we want to test, and updating the result.  Our result comparison is very simple, similar to an Assert.AreEqual.  With a little more work, additional test types could be added, and the test indicated in the test case.

create procedure TestAddWombat as
begin

DECLARE @testcases TABLE(TestNumber INT IDENTITY(1,1) PRIMARY KEY,
TestName NVARCHAR(100),
TestCase NVARCHAR(100),
Expected NVARCHAR(100),
Result NVARCHAR(20));

DECLARE @testname VARCHAR(100);
declare @testcase nvarchar(MAX);
DECLARE @expected NVARCHAR(MAX);
DECLARE @result NVARCHAR(max);

DECLARE @testCaseCount INT;
DECLARE @counter INT;

SET @counter = 1;

INSERT INTO @testcases
( TestName ,
TestCase ,
Expected
)

VALUES  ( N'should_return_hello_wombat' , -- TestName - nvarchar(100)
N'Hello, ' , -- TestCase - nvarchar(100)
N'Hello, wombat'  -- Expected - nvarchar(100)
)

INSERT INTO @testcases
( TestName ,
TestCase ,
Expected
)

VALUES  ( N'should_return_null' , -- TestName - nvarchar(100)
NULL , -- TestCase - nvarchar(100)
NULL -- Expected - nvarchar(100)
)

SELECT @testCaseCount = count(*) FROM @testcases

WHILE @counter <= @testCaseCount

BEGIN

SELECT @testname = TestName,
@expected = Expected,
@testcase = TestCase
FROM @testcases
WHERE TestNumber = @counter

SELECT @result = dbo.AddWombat(@testcase)

IF(@expected is NULL)
BEGIN
IF(@result is NULL)
UPDATE @testcases SET Result = 'True' WHERE TestNumber = @counter
ELSE
UPDATE @testcases SET Result = 'False' WHERE TestNumber = @counter
END
ELSE
BEGIN
IF(@expected = @result)
UPDATE @testcases SET Result = 'True' WHERE TestNumber = @counter
ELSE
UPDATE @testcases SET Result = 'False' WHERE TestNumber = @counter
END
SET @counter = @counter + 1;

END

SELECT * FROM @testcases

end

 

 

Running this sproc returns the following results:

 

image

 

From here, we have a very basic test harness which we can easily add additional test cases, and easily extend.  TDD is a best practice in any aspect of application development, but database development is often not treated as application development and is far behind adopting practices common to C# developers.  This script is a simple way to introduce some TDD into database development.

Getting Started with TDD using MbUnit and C# and VB.NET

There is a lot being written about Test Driven Development in the .NET world.  So much so that it can seem overwhelming to absorb, and you always feel like you’re entering in the middle of the conversation.  Yep, me too, even four years ago when I first dipped my toe into the TDD pool.

After a couple years in SSRS/BI-land, it’s back to regular development, and back into the TDD pool.  The pool has matured–there are new tools, stronger opinions, and much more conversation.  Essentially, I’m starting over, but with a little experience.  In this post, I’ll show you what I’m setting up, and how to get started with a simple project.

What to use?

There are a number of applications available, each with its own adherents and detractors.  Some are free, some you buy.  If you have Visual Studio 2008 Professional or better, you have a testing tool built in.

I tried a couple tools getting started, including NUnit and MbUnit.  I preferred MbUnit, so I downloaded the latest version, part of the Gallio Automation platform.  Gallio/MbUnit is one of the more popular TDD applications.  It’s well supported and well documented, which helps greatly.

So what is Gallio and MbUnit?  Simply understated, MbUnit v3 is a set of classes for unit testing, and Gallio is what actually runs the tests and displays the results.  Gallio can run more tests than just MbUnit, and integrates with build tools.

Installation was a snap–I downloaded the installer and let it run.  Gallio integrated with VS 2005 and VS 2008 automatically, but had an issue loading a language pack when I started VS 2008.

Planning what you’re coding

Before you start hacking away on the keyboard, you need to figure out what you’re writing.  You can use whatever project management methodology you want.  Here’s a user story for something I encountered recently:

For our space utilization analysis, I need to be able to easily take an order date, and figure out the date the week ends on, so I can accumulate inventory changes to the same base date.  I need to be able to specify the end date, since calendar and fiscal weeks may have different end days.  Dates should be calculated back from the given date.  If the the weekday of the given date, and the week end day are the same, then the given date should be used.

Test first!

One of the tenants of TDD is writing your tests first, called the “red, green, refactor” cycle of development.  Red refers to a test which fails, green refers to a test which passes, and refactor refers to reworking the code you wrote to make the test pass to make it leaner.  This takes some realignment of your thought process, but gets easier as you do it.

Open Visual Studio, and create a new project.  I’m calling mine DateLibrary, since I’m actually creating a library of date functions useful to me.  Delete the automatically created Class1.

We need to separate the tests from the code we’ll use in our applications, so our tests don’t end up as part of the application.  Add a second class to the solution, called DateLibraryTests to contain the tests.  Again, delete Class1, and add one named WeekEndTest.  Add references to your class library project, Gallio and MbUnit, and then add using statements, as shown:

C#:

using System;
using DateLibrary;
using MbUnit.Framework;

VB.NET:

Imports System
Imports DateLibrary
Imports MbUnit.Framework

Now, let’s write our first test.  To reiterate, one of the tenants of TDD is writing your tests first.  Realistically, you might need to write a little application code first.  I prefer to have a little Intellisense guide my test writing, so I just stub out a class and methods, just enough so my app will compile, but no actual code.  In our example, we need to return something, so we just return a blank date and time.  Here’s the code stub:

C#:

using System;
namespace DateLibrary
{
    public class WeekEnd
    {
        public static DateTime BackDate(DayOfWeek EndDay, DateTime FromDate)
        {
            DateTime _backdate = new DateTime();

            return _backdate;
        }
    }
}

VB.NET:

Imports System

Public Class DateLibrary

    Public Function BackDate(ByVal EndDay As DayOfWeek, ByVal FromDate As Date) As Date
        Dim _backdate As New Date

        Return _backdate
    End Function

End Class

The first parameter of our method is the day to figure back to, and the second is the date to figure from.  We use a DayOfWeek for the first parameter to limit input values.

One of the criticisms of TDD is that everything you want to test must be public, since tests are placed in a separate class.  So if you plan on having private methods and classes, you’re either out of luck, or you can change the modifier after your tests are run, have public accessors to your private methods, or include test code in your application.  What you do is up to you, based on the local coding standards and application design.  For this sample, public methods are just fine.

Now we write our test.  In TDD, Assert basically means “I am expecting these values…”.  So, our test case could be thought of as “I am expecting these values are equal”.  The values to be compared are the one we’ll calculate from our code above, using inputs which we already know the answer to, and the answer we already know.  Our test looks like this:

C#:

using System;
using DateLibrary;
using MbUnit.Framework;

namespace DateLibrary
{
    public class WeekEndTest
    {
        [Test]
        public void WeekEndsSunday()
        {
            Assert.AreEqual(new DateTime(2009, 06, 21), WeekEnd.BackDate(DayOfWeek.Sunday, new DateTime(2009, 06, 25)));
        }
    }
}

VB.NET:

Imports System
Imports DateLibrary
Imports MbUnit.Framework

Public Class WeekEndTest

    &lt;Test()&gt; _
    Public Sub WeekEndsSunday()
        Assert.AreEqual(CType(<span class="str">"6/21/2009", Date), WeekEnd.BackDate(DayOfWeek.Sunday, <span class="str">"6/25/2009"))
    End Sub

End Class

The [Test] attribute is used by the test runner to find the tests from the regular methods.

Make sure the solution compiles without errors.  If it does, it’s time to run our tests.  Gallio/MbUnit includes the Icarus test runner, which provides easy to read graphical feedback.  To start Icarus, navigate Start >> All Programs >> Gallio >> Icarus GUI Test Runner.  Once Icarus has started, we need to load in our tests.  Go to Project >> Add Assemblies, navigate to the bin folder of your test project, select the test DLL and click Open.

SS-2009.06.27-15.15.12

The tests will be parsed, and listed in a treeview.  You can add as many test DLLs as you need, we just have the one for this example.

SS-2009.06.25-22.30.57

Click the Start button, and our tests will be run.  The Execution Log shows the results, in this case, red, just like we expected.

SS-2009.06.25-22.34.37

Further down, you can see the expected and returned values.  Our test failed (as expected) because a blank date was returned, and did not match the expected value.

SS-2009.06.26-20.26.48

Great.  Like we said above, “red, green, refactor”.  We now have red taken care of.  Before we do anything else, let’s do a quick sanity check to make sure all systems are correct.  In our test, we’re expecting the returned value to be 6/21/2009.  As a test of our tests, let’s change our procedure slightly to make sure it returns the expected date, and run our test again.  The results should be green.

C#:

using System;

namespace DateLibrary
{
    public class WeekEnd
    {
        public static DateTime BackDate(DayOfWeek EndDay, DateTime FromDate)
        {
            DateTime _backdate = new DateTime(2009, 06, 21);

            return _backdate;
        }
    }
}

VB.NET:

Imports System

Public Class WeekEnd

    Public Shared Function BackDate(ByVal EndDay As DayOfWeek, ByVal FromDate As Date) As Date
        Dim _backdate As New Date

        _backdate = "6/21/2009"

        Return _backdate
    End Function

End Class

Sure enough, our results are green.  This does not satisfy the green portion of “red, green, refactor”, this is merely to confirm our systems are working correctly.

SS-2009.06.26-20.34.03

Now it’s time to write some actual application code, and have our test be green for real.  From the user story above, we arrive at the following code:

C#:

using System;

namespace DateLibrary
{
    public class WeekEnd
    {
        public static DateTime BackDate(DayOfWeek EndDay, DateTime FromDate)
        {
            DateTime _backdate = new DateTime();
            int _dayOfWeek = new int();

            _dayOfWeek = (int)EndDay;

            if (FromDate.DayOfWeek == EndDay)
            {
                _backdate = FromDate;
            }
            else if ((int)FromDate.DayOfWeek &gt; (int)EndDay)
            {
                _backdate = FromDate.AddDays(-(int)FromDate.DayOfWeek - (int)EndDay);
            }
            else
            {
                _backdate = FromDate.AddDays(-(int)EndDay - (int)FromDate.DayOfWeek);
            }

            return _backdate;
        }
    }
}

VB.NET:

Imports System

Public Class WeekEnd

    Public Shared Function BackDate(ByVal EndDay As DayOfWeek, ByVal FromDate As Date) As Date

        Dim _backdate As New Date()
        Dim _dayOfWeek As New Integer()

        _dayOfWeek = CInt(EndDay)

        If FromDate.DayOfWeek = EndDay Then
            _backdate = FromDate
        ElseIf CInt(FromDate.DayOfWeek) &gt; CInt(EndDay) Then
            _backdate = FromDate.AddDays(-CInt(FromDate.DayOfWeek) - CInt(EndDay))
        Else
            _backdate = FromDate.AddDays(-CInt(EndDay) - CInt(FromDate.DayOfWeek))
        End If

        Return _backdate
    End Function

End Class

Now, we run our test again, to make sure our code does what we think it will do:

SS-2009.06.27-15.21.09

 

Success!  This time, we’re green for real.  The final part of the TDD is to refactor.  Refactoring is a process of editing your code to make it more concise, more maintainable, and more reusable.  It’s not an entirely simple process, requiring a good deal of thought.  The best part about TDD is that as you refactor, you can easily tell if you’ve broken your code by running your tests again.  It’s a process we won’t cover here.

 

 

 

 

For more information

To learn about and download MbUnit and Gallio, go to http://www.gallio.org/.

For a deeper look into TDD, the book recognized as starting it all is Test Driven Development By Example, by Kent Beck.  The examples are in Java and JUnit, but this is the book recognized as the authoritative work on TDD.

If you’d prefer to have examples in .NET, you might like Test Driven Development in Microsoft .NET.  James Newkirk is a leader in Agile development, and is one of the founders of NUnit and xUnit.

DotNetKicks Image

Using Mocks and Stubs and Free TypeMock Licenses

Blogs are a fabulous way to share knowledge, and for incremental learning, but they’re a tough way to grok an entire underlying concept.  Something I’ve never quite grokked are mocks and stubs, until last week.  My favorite development mag’s latest issue is dedicated to open source tools in .NET development.  One article focuses completely on mocks and stubs, Isolating Dependencies in Tests Using Mocks and Stubs.  After reading this, I thought "wow, I need to get me a mocking framework".  One of the popular mock frameworks mentioned was TypeMock.

Enter TypeMock today.  A short time ago, I get this in my FeedDemon:

Unit Testing ASP.NET? ASP.NET unit testing has never been this easy.
Typemock is launching a new product for ASP.NET developers – the ASP.NET Bundle – and for the launch will be giving out FREE licenses to bloggers and their readers.

The ASP.NET Bundle is the ultimate ASP.NET unit testing solution, and offers both Typemock Isolator, a unit test tool and Ivonna, the Isolator add-on for ASP.NET unit testing, for a bargain price.

Typemock Isolator is a leading .NET unit testing tool (C# and VB.NET) for many ‘hard to test’ technologies such as SharePoint, ASP.NET, MVC, WCF, WPF, Silverlight and more. Note that for unit testing Silverlight there is an open source Isolator add-on called SilverUnit.

The first 60 bloggers who will blog this text in their blog and tell us about it, will get a Free Isolator ASP.NET Bundle license (Typemock Isolator + Ivonna). If you post this in an ASP.NET dedicated blog, you’ll get a license automatically (even if more than 60 submit) during the first week of this announcement.
Also 8 bloggers will get an additional 2 licenses (each) to give away to their readers / friends.

Go ahead, click the following link for more information on how to get your free license.

Serendipity, baby!  Hope I’m one of the first 60.

DotNetKicks Image

TestDriven.NET Enterprise & Professional

In order to help turn TestDriven.NET from a hobby into somethig that pays the
bills, Jamie has released a Professional and Enterprise version of
TestDriven.net. There’s still a free version, non-crippled and non-nag, but he
asks if you regularly use TestDriven.NET in one of the licensing scenarios,
please buy a license.

Full story at http://weblogs.asp.net/nunitaddin/archive/2006/07/10/EnterpriseAndProfessional.aspx.

kick it on DotNetKicks.com