Unit tests: Applying test-driven development to your database project

This article discusses:

The advantages of TDD

Applying unit tests to database development

Combining T-SQL and. NET-compatible languages

connections, test conditions, and transactions

This article uses the following techniques:

Visual Studio 2008, SQL Server

Lmicrosoft the Visual studio®team System database Edition, also known as DBPro or data Dude, in November 2006, which introduced database development to the product lifecycle approach. DBPro also introduced the Database Unit Test Designer, which makes it easy to generate or write T-SQL-oriented cells that validate database objects before development. In this article, I'll delve into the database unit tests, explain how to use the functionality, and show you how to perform unit tests on your own database project.

Unit testing in software development

In software development, unit testing plays an important role in ensuring quality levels and meeting milestone requirements. Although many developers recognize the importance of unit testing, most of them do not spend time writing unit tests at the end of the product cycle. To solve this problem, the test-driven Development (TDD) approach came into being. Developers can use this method to write unit tests of a single feature before they actually apply functionality. This allows developers to view the APIs from outside to the inside, simplifying the design as much as possible.

Today, many developers using C # and Visual basic® are guided by TDD, creating unit tests to try out methods on classes that have not yet been written, and then compiling the tests with the least amount of code. If the compiled test fails, the developer simply writes the code necessary to pass the next round of tests. All unit tests are run at each iteration to verify that no regression errors have occurred. The application and unit test code is then checked in to the source code control. Each unit test runs two times for the method that is being developed.

To be fully functional, unit tests must set up their own execution environment before they run in order to stably validate their specially tested code. After you have implemented and validated the test, check it in to the source control and use it in the remainder of the product lifecycle to ensure that the method continues to be used as expected.

Because you need to provide a stable test environment to validate code before checking in changes, TDD becomes more challenging in database development. In order for a database unit test to run successfully, it must have the appropriate schema and data. This means that for running tests, other developers must reuse the environment that you use when they create unit tests. This is hard to achieve without a lot of expensive infrastructure. Fortunately, DBPro allows developers in this environment to follow the same unit testing process.

Get the latest test and database project from the source control, and then add the newly stored procedure to your database project. The input and output parameters of the configuration process.

Right-click the newly stored item, and select Create unit tests. Add a unit test to an existing test project. The new test class joins a test project with T-SQL, and T-SQL executes the new stored procedure with the default input parameters.

Modify the input parameters to include the expected value, and then add test conditions to verify that the stored procedure returns the expected results.

Run the test. When you test the settings, deploy the database project to a local SQL server® instance, and execute the data generation plan to populate the newly deployed database with the expected test data. Testing is performed against the local SQL Server instance, which is the expected result because the logic of the stored procedure has not yet been executed.

Execute the logic of the stored procedure and run the test again. Deploy the updated stored procedures and pass the tests.

Run all tests to validate the database, and then check the stored procedures and unit test code into the source code control.

DBPro provides features such as generating stub T-SQL unit tests from existing functions, stored procedures, or triggers, automatically deploying database project updates to sandboxed instances, using data generation plans to generate data during test environment setup, and executing T-SQL Tests against the target database. Although these functions can be used together, they do not require this. For example, you can write database unit tests from scratch without having to generate data before each test run.