April 16, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Introducing Visual Studio Team System 2008 Database Unit Testing

  • July 14, 2008
  • 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 = '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
       );

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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel