Introducing Visual Studio Team System 2008 Database Unit Testing
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?
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).
Figure 2: Create Project dialog