How To: Create DB Unit Test With Team System

How To: Create DB Unit Test With Team System

In my last post I talked about What Is Database Unit Testing and Why? , in this post I'll show how to create DB Unit Test.

Building on the AdventureWorks sample project from VSTSRTM08-V7 VPC, I'll unit test a modified version of the uspLogError stored procedure.

--Removed the error check
IF @dbVersion = '9.04.10.13.00'
BEGIN
   IF ERROR_NUMBER() IS NULL
      RETURN;
END

...
INSERT [dbo].[ErrorLog]
       (
       [UserName],
       [ErrorNumber],
       [ErrorSeverity],
       [ErrorState],
       [ErrorProcedure],
       [ErrorLine],
       [ErrorMessage],
       [DBVersion]
       )
   VALUES
       (
       CONVERT(sysname, CURRENT_USER),
       ISNULL(ERROR_NUMBER(),0),   --Added ISNULL check for error
       ERROR_SEVERITY(),
       ERROR_STATE(),
       ERROR_PROCEDURE(),
       ERROR_LINE(),
       ISNULL(ERROR_MESSAGE(),''), --Added ISNULL check for error
       @dbVersion
       );

Adding a Unit Testing Project

Adding a test project is straightforward; simply add a project to the solution. Select a Test Project from the Project templates dialog

Adding Database Unit Test

Like all Visual Studio projects, you right-click on the project and select "Add Test" or "Add Item" to create new items in the project. The dialog below appears when you select "Add Test"

Because I'm creating the first Database Unit Test in the project, I'll be prompted to set database configuration information for the whole project.
You must at least select a database connection to configure a project.
I selected the database maintained by the AdventureWorks database project.
Therefore, as I change and edit the project the underlying database I'm using in the unit test will also change.

A database unit test can be quite sophisticated.
More advanced testing scenarios, though, are beyond the scope of this article.

Once you complete the configuration information the Database Unit test will look much like the test shown

Add the following T-SQL to the main editor window in the designer:

DECLARE @ErrorLogID int
SET @ErrorLogID =1

EXECUTE [dbo].[uspLogError]
    @ErrorLogID OUTPUT

Select [ErrorLogID]
    ,[ErrorTime]
    ,[UserName]
    ,[ErrorNumber]
    ,[ErrorSeverity]
    ,[ErrorState]
    ,[ErrorProcedure]
    ,[ErrorLine]
    ,[ErrorMessage]
FROM [dbo].[ErrorLog]

This query will show all the errors in the AdventureWorks database.

My unit test will check if the Error Log is empty.

Click the inconclusive test condition in the Test Conditions panel, in the bottom half of the designer. Click the red "x" button to delete the test condition.

Add a row-count test condition by clicking Row Count in the Test Conditions list and clicking the + button.

In the Properties window, set the number of expected rows to 0.

On the Test menu, point to Windows, and click Test View.

Right-click the test, and click Run Selection.

Review the results in the Test Results window.

And your test passed!

You have just successfully created your first database unit test.

Download TestProject.zip