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).
Figure 2: Create Project dialog
Aside from adding the project to your solution, Visual Studio adds some additional solution components separate from the project (see Figure 3).
Figure 3: Additional solution components
The files control your testing experience and allow you to configure more complicated testing scenarios. Neither of these files will be part of this articles demonstration, but you can find more details in the resources at the end of the article.
The test project appears in Figure 4.
Figure 4: Sample Project components
The project is created with a default Manual Unit Test and a blank Automated C# Unit Test. These files can be deleted, but I’m leaving them in so you can see how tests are grouped and execute alongside each other later in the article. The AuthoringTests.txt file explains how to navigate the project and how to configure some of the Unit Testing options.
As you can see, I’ve created a Test project for Database Unit testing, but there are no Database tests in the project, so I’m going to add one and configure it to test my TSQL code.
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” (see Figure 5).
Figure 5: Add New 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. As you can see in Figure 6, for simplicity, 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.
Figure 6: Set database configuration
A database unit test can be quite sophisticated. Later in the article, I’ll show you how to include data in your test. 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 in Figure 7.
Figure 7: Empty Database Unit Test
Like Unit Testing with other types of projects, you write Database Unit Tests in the same language you use for development. In the Database Unit Test, the development language is TSQL.
Most likely, you’ll need to operate on some data to execute your tests. So, before I develop the Unit Test, I’m going to create some sample data and later incorporate the data in the Unit Test.
Creating Sample Data
Tests often must be repeated with the same data. Therefore, a Database Unit Test needs a way to repeatedly load the same data. So, first I’ll create a Data Generation Plan and then wire the Data to the Database testing project.
Data Generation Plan creation happens inside the Database Development project. Again, select the “Add Items” option on the Database Project. The dialog shown in Figure 8 appears.
Figure 8: Add Data Generation Plan
The initial Data Generation plan appears in Figure 9.
Figure 9: Data Generation Plan
My demonstration focuses on the AWBuildVerion table, so I’m deselecting all but the AWBuildVersion table and the ErrorLog table. Although I’m not initially loading data into the ErrorLog, I’m including it in the plan so that a purge data option I’ll select later removes all data from the table before running the test.
Deselected tables are not removed until currently selected tables dependent on the deselected table are in turn deselected. The behavior looks very much like Figure 10.
Figure 10: Deselect tables
There are many ways to generate sample data. By using Regular Expression options, you can even create data to look like common data patterns such as Social Security Numbers and phone numbers. I selected a Data Bound Generator to a SQL Server database along with an appropriate SELECT statement (see Figure 11).
Figure 11: Databound generator configuration
With sample data, I’m ready to configure the Test.
Setting Test Conditions
First, I’m returning to the TestProject Database Test configuration on the Test menu. I need to make the Data Generation Plan part of the project configuration (see Figure 12).
Figure 12: Incorporate Data Generation Plan
Now, I’ll construct the test. The TSQL test code appears in Figure 13.
Figure 13: TSQL test code
As you can see, the second statement in the test code returns a result set. To verify that the test executed properly (see Figure 14), I’m going to inspect some of the properties of the returned result set.
Figure 14: Test conditions configured
I’ve added two test conditions. One checks the RowCount of the result set returned by the SELECT statement and the other verifies that the DBVersion field in the ResultSet returned by the SELECT statement equals “10.0”, the version number I’ve set in the test data.
Executing the test is managed from the test menu.
Running the Test
There are a number of ways to manage and run groups of tests. A complete review is beyond the scope of this article. I’m going to use the Test List Editor to run the tests. Test List Editor can be found on the Test menu located under Windows selection as shown in Figure 15.
Figure 15: Test List Editor location
The Test List Editor appears in Figure 16.
Figure 16: Test List Editor
You can choose to run tests individually or in groups. After you invoke the test you’ll get results pictured like the list shown in Figure 17.
Figure 17: Test Run results
Conclusion
Unit Testing is now firmly entrenched in Visual Studio Team System. Even database developers can take advantage of Unit Testing. Unit Test project features work like other Visual Studio and Visual Studio Testing features.
Resources
- Advanced article on how to build custom test conditions, plus additional resources: http://msdn.microsoft.com/en-us/magazine/cc164243.aspx
- Unit Testing Basics, C# example: http://msdn.microsoft.com/en-us/library/ms379625(VS.80).aspx
- Definition: http://en.wikipedia.org/wiki/Unit_test
Download the Code
You can download the code that accompanies this article here. (It is a large file.)
About the Author
Jeff Juday is a software developer specializing in enterprise application integration solutions utilizing BizTalk, SharePoint, WCF, WF, and SQL Server. He has been developing software with Microsoft tools for more than 15 years in a variety of industries, including military, manufacturing, financial services, management consulting, and computer security. Jeff is a Microsoft BizTalk MVP. Jeff spends his spare time with his wife Sherrill and daughter Alexandra. You can reach Jeff at me@jeffjuday.com.
