Unit Testing with Visual Studio DB Pro Part 2

by Greg M Lucas on 21 November 2010

In Part 1, I talked about some of the challenges associated with writing effective database unit tests and in Part 2, we will work through setting up a new database test project in Visual Studio 2008 then writing and passing our first unit test.

If you’re reading this I will assume you know about some of the possible benefits of test-driven development or TDD (a.k.a. test-first development) and so will quickly cover first principles.  The idea behind TDD is that before you write a line of code, you write a test to prove the code you’re planning to write actually works.  Only when you have a failing test do you write just enough code to pass that test.  You continue in this fashion until you have passing tests covering everything that function needs to do for the current iteration or release.  This has hopefully allowed you to think about the code you’re writing from the outside or consumers perspective and if your tests only cover the current, known requirements you won’t have wasted time writing any extra “just in case they want it in the future” features which probably never get used anyway.  You know you’ve done this, I know I have!

Unit testing for databases falls in to a number of categories – there are simple schema/object tests to make sure that everything you expect to be there does actually exist; there are rules-based testing, for example “does every table have a unique key?”; and then there are the logic tests – checking that stored procedures and functions behave as expected – ether making the expected data changes or returning the correct result sets.

I’m currently playing with an idea for a simple, open source logging and exception handling block for SQL2005+ called Log4TSql (which I may even finish one day!).  I thought that this simple database might be a good place to start demonstrating some tests.  You can download all the code and tests from the Log4TSql site at SourceForge although at the time of writing it’s not yet finished.

I do try and adhere to proper test-driven development (TDD) when coding in C# so will try and do the same thing for this database project.  In Visual Studio 2008 Team System for Database Professionals (DBPro) I’ve created a solution called Log4TSql and within that an empty database project called Log4TSqlDB.

So the first thing I need to do is create my first test, and in doing so create a database test project at the same time.  Click on Test, and then New Test

Starting a new database test project in VS2008 DB Pro

 This opens the Add New Test window.  For our purposes, choose Database Unit Test then specify a test name.  This is actually a bit of a misnomer as the name you provide actually represents a container for one or more tests.  The first table I want to create will allow me to record SQL Server exceptions, so the test name I’m using is ExceptionTests.cs. The “cs” extension just means that under the hood, DBPro will create a C# class file for this group of tests.

Adding a new test to a new test project in VS2008 DB pro

Notice that as we don’t yet have a test project, the Add to Test Project option is indicating that a new C# test project will be created.  You can also create one in VB if you prefer.  Since you don’t need to actually write or even understand C# or VB.NET to create these simple tests you can choose either.  Click OK and you will first be asked to specify the name of your test project. In the resulting New Test Project window (not shown), clicking on Create will create a new database test project and then prompt you to specify a database connection to use for this group of tests.

If you don’t already have a database on your local development instance and the associated connection, create the database now and then the connection when prompted.

Specify that connection and then tick the box for Automatically deploy the database project before unit tests are run and select the database project you will be writing tests for.  This ensures that your local database instance will always be the most up to date – which is usually what you want for unit-testing.  For Deployment configuration select Default for now then click OK.

Setting up a new data connection in a VS2008 DB pro test project

Your database project has now been set up and you will see an empty project like this:

An empty test project in VS2008 DB Pro

Note that our solution now has a new project: Log4TSqlTests and that it already contains an ExceptionTests class.  Click on the Click here to create link in the middle of the window to create your first actual test.

Each test that you create has a SQL element and a set of one or more conditions – all of which must be met before the test(s) pass.  You do not have to write a line of C# or VB.NET to create tests – the environment does all that for you.

The SQL script can be used to return one or more result sets, against which you can define test conditions – such as:

  • The first result set must be empty
  • The second result set must have exactly 5 rows
  • The value in the second column of the third row must be “Fred”

The simplest test we can write at this stage is “Does the Exception table exist?”  The following example SQL will allow us to test that condition.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Exception'
AND TABLE_TYPE = 'BASE TABLE';

You will notice that one test condition already exists by default.  Running the tests now would give neither a pass nor a fail.  So delete that test by clicking on the red cross indicated.

Writing the SQL to test whether a table exists in VS2008 DB pro

Click on the Test Conditions drop down to review the types of test condition available, most are self explanatory except “Scalar Value” – this doesn’t imply testing a result set of one row with one column – rather it can be used to validate a single cell value in a multi-row, multi-column result set.

Choosing a test condition in VS2008 DB Pro

 Select “Row Count” from this list and then click the green plus to add a new test condition.

Edit the resulting Properties window so it looks like this. If you can’t see a Properties window, right-click the row you just added (called rowCountCondition1) and select Properties.

Test Conditions Window in VS2008 DB Pro

Your Test Conditions grid should now look like this:

Test Conditions grid in VS2008 DB Pro

You are now ready to run your first test which should fail as the Exception table doesn’t yet exist. There are several ways to kick off these tests, for example, click Test on the top level menu, then under Run, click All Tests in Solution.

Running a database test from the menu options in VS2008 DB pro

You can also run one or more tests from the buttons like this:

Running a database test from a button in VS2008 DB Pro

When you first run this test, you should see a failure like this:

Failing Test notification in VS2008 DB pro

Click on the Test run failed for more information:

Failing test detail screen in VS 2008 DB pro

The minimum code we can write to pass this test is to create a table with just one column. In strict TDD terms, this one column should probably allow null at this point but I know that this column will become the primary key in due course so I’m leaving it as NOT NULL.

Writing just enough code to pass a database unit test in VS2008 DB pro

Now, re-run our test – which should pass with the following result:

Passing a database unit test in VS2008 DB pro

So, we have successfully set up a new database test project, written our first unit test and just enough code to pass that test.  In Part 3 we will add some more tests to check that the Exception table has a primary key and that the value of the primary key column increments automatically.

Comments on this entry are closed.

Previous post:

Next post: