February 25, 2021
Hot Topics:

Introducing Visual Studio Team System 2008 Database Unit Testing

  • By Jeffrey Juday
  • Send Email »
  • More Articles »

Unit Testing is now a fixture on the Microsoft Visual Studio landscape. Almost every type of application development has some sort of specialized Unit Testing feature built into Visual Studio. Even SQL Server database development has Unit Testing.

In an earlier article, I introduced the Visual Studio Team System Database edition. In this article, I'm going to introduce you to database Unit Testing project built into Visual Studio Team System.

Unit Testing a Database?

You may be wondering, why Unit Test a Database; isn't this overkill? For a trivial database, yes Unit Testing may be overkill, just like unit testing a trivial application may be overkill. So, though a database may be developed different from, for example, a desktop application, you Unit Test for the same reasons you Unit Test any other application.

In particular, you Unit Test to exercise all your code and verify that the relationships among all parts of your code, at present, and in the future; adhere to what you originally intended. Here are some sample scenarios.

Often, a developer needs to assemble some type of scaffolding to test a stored procedure or view. Unit Tests can be the basis of the scaffolding. Unlike sample applications written by the original developer and then discarded, Unit Tests can be checked in with the developed code and utilized by other developers.

Unit Tests can be configured with a whole range of data sizes and shapes, at the very edges of what is allowed in the database. An application may not exceed size limits in a database until it is ported to, for example, a new language. Often, a developer will not be aware that a stored procedure parameter is bigger than a particular field it is writing to a table until the upper limits are reached.

Unit Tests serve as living documentation and may answer questions like: How you intended to use a Stored Procedure? and How fast should a particular query execute?

Sample Code

Building on the AdventureWorks sample project from my prior article, I'll unit test a modified version of the uspLogError stored procedure.The code appears in Figure 1.

--Removed the error check
IF @dbVersion = ''

INSERT [dbo].[ErrorLog]
       CONVERT(sysname, CURRENT_USER),
       ISNULL(ERROR_NUMBER(),0),   --Added ISNULL check for error
       ISNULL(ERROR_MESSAGE(),''), --Added ISNULL check for error

Figure 1: uspLogError changes

I commented out the ERROR_NUMBER() check, allowing the code to continue execution with or without an error. I also modified the Error Log to include the Database Version in the error log code.

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 (see Figure 2).

Click here for a larger image.

Figure 2: Create Project dialog

Page 1 of 5

This article was originally published on July 14, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date