MSDN Magazine Launch Issue - February 15, 2008 - (Page 66) is checked into source code control and will be used for the rest of the product’s lifetime to ensure that the method continues to behave as expected. TDD is more challenging in database development because you need to provide a consistent test environment to verify code prior to checking in changes. For a database unit test to run successfully it must have the appropriate schema as well as the proper data. This means that in order to run tests, other developers DBPro provides features that must duplicate the environallow database developers ment you used when the unit tests were created. This to follow the exact same is a tall order to fill without unit-testing process that building a lot of costly inother developers follow. frastructure. Fortunately, DBPro provides features that allow database developers to follow the same unit-testing process that other developers follow, as in this scenario: 1. Get the latest test and database projects from source code control and add a new stored procedure to the database project. Configure the procedure’s input and output parameters. 2. Right-click on the new stored procedure and select “create unit tests.” Add the unit test to the existing test project. A new test class is added to the test project with T-SQL that will execute the new stored procedure with default input parameters. 3. Modify the input parameters to contain expected values and add a test condition to verify that the stored procedure returns the expected results. 4. Run the test. As part of test setup, the database project is deployed to the local SQL Server® instance and the data generation plan is executed to populate the newly deployed database with expected test data. The test executes against the local SQL Server instance and fails (as expected because the stored procedure’s logic has not yet been implemented). 5. Implement the stored procedure’s logic and run the test again. The updated stored procedure is deployed, and the test passes. 6. Run all tests to verify the database and then check the stored procedure and unit-test code into source code control. The features provided by DBPro include: the generation of stub T-SQL unit tests from existing functions, stored procedures, or triggers; automatic deployment of database project updates to a local sandbox instance; generation of data as part of the test environment setup using a data generation plan; and execution of T-SQL tests against a target database. Although these features can be used together, that isn’t required. For instance, a database unit test can be written from scratch and data generation does not have to be executed prior to every test run. reconciled by encapsulating the test in T-SQL code and then hosting the T-SQL in C# or Visual Basic .NET code. The unit-test designer generates both, enabling either (or both) to be modified to meet individual needs. Database unit testing with Team System provides or supports the features you’d want if you were designing a database testing framework yourself. You need to be able to execute a test with the proper context. For example, you need to utilize the credentials your Web tier would use when connecting to the database or be able to use a different set of credentials to access or modify the underlying tables before and after a test has executed. Being able to run a script before and after each test in a “test group” to set up or reset the database state is helpful, as is the ability to run a script (for an individual test) before and after a test to set up or reset the database state. Finally, you’d like to extend the set of test conditions available by adding conditions specific to your database, leverage transactions to control modifications to database state, and use data to drive multiple runs of your test with different input and output parameters. In some cases, to use these features you’ll need additional code. Let’s see what that entails. Defining Connections As I mentioned, it’s important for a database test harness to allow tests to execute using a connection other than the one used to verify and set up the database. To support this goal, Microsoft created the concept of a connection context to contain all the information necessary to define the connection and how it should be used to execute the test. The base connection context contains an open connection, the provider factory (msdn2.microsoft.com/system.data.common.dbproviderfactory) to use when creating additional ADO.NET objects, as well as the timeout and DbTransaction to use when executing a test. Using the context type, you define two context instances—execution context and privileged context—to support test setup, execution, and verification. As is clear from its name, execution context Figure 1 Test Scripts and Their Execution Script Test Initialization Execution This T-SQL script is used to set up the test environment prior to each test’s execution. This script is executed before every test in the class. The setup prior to a specific test being executed. The pre-test script is available so that you can perform a set-up action for the test or verify that the database is in the appropriate state for the test to take place. The test itself. The cleanup after a specific test has executed. The post-test script is supplied so that you can restore or verify the state of the database. This T-SQL script is used to restore the test environment after the test has been completed. This script is executed after every test. Pre-Test Setup Database Unit Testing Test Post-Test Cleanup The database unit-testing feature reflects two somewhat competing goals: the desire on the part of the database developer to use familiar T-SQL scripts to express the unit-test logic and the desire to take advantage of powerful Microsoft® .NET Framework-targeted languages such as C# or Visual Basic. These competing goals are 66 msdnmagazine Database Unit Testing Test Cleanup http://msdn2.microsoft.com/system.data.common.dbproviderfactory http://msdn2.microsoft.com/system.data.common.dbproviderfactory
For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.