SQL Geeks Indonesia

Database Unit testing with SQL Server 2005

As you seen on my previous articles in my blog,  Unit testing is very importan in software development. But people still not aware about this because developer commonly only focus on how to develop the application and deprioritize the quality of code it self. Unit testing will make sure system behaviour and response from every parameter input that given by user will compliant with the function spesification.

To help developer doing unit testing in stabilizing phase, Visual Studio team system for database already have capabilty to create and develop module for unit testing.

The framework in Microsoft Visual Studio 2005 Team Edition for Database Professionals (DB Pro) for database unit testing, however, offers some important value-add features above and beyond those just mentioned. The first of these is automatic generation of SQL script stubs of unit tests for stored procedures, functions, and triggers. This feature saves you the hassle of developing the boilerplate code that you see earlier in this paper.

Secondly, DB Pro provides a set of built-in test conditions to help you verify your test results. These test conditions perform the most common validation that you would want to verify, including the rows returned, scalar values, and execution time. You can easily configure these test conditions through the user interface.

And, most importantly, DB Pro offers a way for you to set the database state by using the data-generation feature to populate the database with test data before running your database tests. The beauty of the data-generation functionality is that you can repeatedly generate the same test data based on a seed value, making it repeatable and thus very applicable for unit testing.

We will explore each of these features in this paper and how best to leverage them in implementing your process for database unit testing.

Authoring Your First Database Unit Test

To get you started, the following simple walkthrough shows how you can create a database unit test for the CustOrderHist stored procedure in the Northwind database.

1. Ensure that Team Edition for Database Professionals is installed on your computer.
2. Open Microsoft Visual Studio.
3. On the Test menu, click New Test.
4. In the Add New Test dialog box, click Database Unit Test, and pick the type of test project that you want to create (either C# or Visual Basic).

   

   Figure 1. Add New Test dialog box

5. Specify a name for the new test project.

   

   Figure 2. New Test Project dialog box

6. Specify the database connection against which to run the test, and click OK.

   

   Figure 3. Database test configuration

   The Database Unit Test Designer appears.

   

   Figure 4. Database Unit Test Designer (Click on the picture for a larger image)

7. Add a test method by clicking the plus sign (+) on the top of the designer.

   

   Figure 5. Adding new database unit test

8. Name the new test, and click OK.

   

   Figure 6. Naming new database unit test

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

   DECLARE @CustomerId nchar(5)
   SELECT @CustomerId = 'EASTC'
   EXEC dbo.CustOrderHist @CustomerId
   

10. 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.

    

    Figure 7. Test Conditions panel

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

    

    Figure 8. Adding row-count test condition

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

    

    Figure 9. Configuring test-condition properties

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

    

    Figure 10. Running test from Test View

14. Right-click the test, and click Run Selection.
15. Review the results in the Test Results window.

    

    Figure 11. Viewing test results

And your test passed!

You have just successfully created your first database unit test. Let's now drill into the details of the various phases of database unit testing.

source : http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx#dtbunttsttedp_topic1

Share this post: | | | |