Developing and implementing unit tests and test suites

Summary: Microsoft Visual Studio 2005 Team Edition for Database Professionals is the first toolset to deliver a comprehensive framework for performing database unit tests. This product brings to the data community the same first-class capabilities for performing unit tests that many application developers enjoy today. This paper explains exactly what database unit testing is, shows you how to develop database unit tests by using the framework, and walks you through how to leverage its rich capabilities to develop an effective strategy for performing database unit tests inside your organization.

What Is Database Unit Testing?

Unit testing is a well-understood concept in application development, but the database community has not yet embraced the advantages and strategies of this approach. Therefore, I'd like to start by exploring the fundamental tenets of the unit-testing methodology. Unit testing provides a structured and automated way of testing individual components of a system. Unit tests are most often authored by the developer of the component that is being tested. Each unit test tests a specific module of the code in an isolated fashion to ensure that the component behaves as expected.

How does this all relate to database development? The direct analog of application unit tests in the database world are tests of a database's programmability objects. These objects include, for example, a database's stored procedures, functions, and triggers.

What might a unit test for a stored procedure look like? Let's say that you are trying to test the CustOrderHist stored procedure in the Northwind database. The stored procedure should return the order history for a given customer ID. To test this behavior, you can imagine writing a SQL script that executed the stored procedure and then verified whether the expected number of rows was returned. Such a script might resemble the following:

DECLARE @CustomerId nchar(5)

SELECT @CustomerId = 'EASTC'

EXEC dbo.CustOrderHist @CustomerId

IF (@@ROWCOUNT <> 19)

RAISERROR('Actual Rowcount not equal to expected 19',11,1)

Why Perform Database Unit Tests?

As a methodology, unit testing has many advantages over manual, ad-hoc testing and debugging. By developing database unit tests, you can create a collection of tests and run them during development to ensure that your features work as you expect. Because each unit test focuses specifically on an individual method, you can more easily determine the source of a failure for a failing unit test. Therefore, database unit tests help you determine the sources of bugs in your code.

Such a collection of tests is very useful for regression testing. As you implement new features, you can rerun existing tests to ensure that existing functionality has not been broken. Such a regression test suite facilitates database changes, because you can now make changes knowing the implications of those changes.

Unit tests, in addition, serve as documentation for users of the methods under test. Developers can quickly review unit tests to determine exactly how particular components should be consumed.

Types of Database Unit Tests

Database unit testing is not limited merely to testing the database's programmability objects. You might want to author the four classes of tests that this section describes.

Feature Tests

The first and likely most prevalent class of database unit test is a feature test. In my mind, feature tests test the core features—or APIs, if you will—of your database from the database consumer's perspective. Testing a database's programmability objects is the mainline scenario here. So, testing all the stored procedures, functions, and triggers inside your database constitute feature tests in my mind. To test a stored procedure, you would execute the stored procedure and verify that either the expected results were returned or the appropriate behavior occurred. However, you can test more than just these types of objects. You can imagine wanting to ensure that a view, for example, return the appropriate calculation from a computed column. As you can see, the possibilities in this realm are large.

Schema Tests

One of the most critical aspects of a database is its schema, and testing to ensure that it behaves as expected is another important class of database unit tests. Here, you will often want to ensure that a view returns the expected set of columns of the appropriate data type in the appropriate order. You might want to ensure that your database does, in fact, contain the 1,000 tables that you expect.

Security Tests

In today's day and age, the security of the data that is stored within the database is critical. Thus, another important class of database unit tests are those that test the database security. Here, you will want to ensure that particular users exist in your database and that they are assigned the appropriate permissions. You will often want to create negative tests that attempt to retrieve data from restricted tables or views and ensure that the access is appropriately denied.

Stock-Data Tests

Many databases contain stock data, or seed data. This data changes infrequently and is often used as lookup data for applications or end users. ZIP codes and their associated cities and states are great examples of this kind of data. Therefore, it is useful to create tests to ensure that your stock data does, in fact, exist in your database.

Benefits of Team Edition for Database Professionals

How might a tool help you implement this methodology? As you would expect, such a tool would facilitate running, managing, and reporting the results of database unit tests.

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

5. Specify a name for the new test project.

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

The Database Unit Test Designer appears.

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

8. Name the new test, and click OK.

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

10. DECLARE @CustomerId nchar(5)

11. SELECT @CustomerId = 'EASTC'

12. EXEC dbo.CustOrderHist @CustomerId

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

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

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

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

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

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.

Test Authoring

Team Edition for Database Professionals delivers a unique experience for authoring database unit tests through the Database Unit Test Designer. By using that designer, you can easily add, delete, or modify database unit tests in an easy-to-use interface. As the following sections describe, three important aspects of test authoring include automatic test script generation, test validation, and test setup and cleanup.

Automatic Test Script Generation

One of the powerful features of Team Edition for Database Professionals is its ability to automatically generate a stub SQL test script for stored procedures, functions, and triggers.

By using this feature, you can right-click an existing stored procedure, function, or trigger in your database project in Solution Explorer, click Create Unit Tests, and automatically generate a stub test script. This approach saves you considerable time in generating the boilerplate code you will often need for your test.

Of course, you will then need to customize the test script. At a bare minimum, you must specify the parameters to the stored procedure, function, or INSERT,UPDATE, or DELETE statement (for triggers). You then must add appropriate test verification.

Let's take a closer look at exactly what kind of test script is generated for each of the three types of programmability objects.

Stored Procedures

The following is the generated test script for the dbo.CustOrderHist stored procedure in the Northwind database:

-- db unit test for dbo.CustOrderHist

DECLARE @RC INT,

   @CustomerID NCHAR (5)

SELECT @RC = 0,

   @CustomerID = NULL

EXEC @RC = [dbo].[CustOrderHist] @CustomerID

SELECT RC=@RC

As you can see, the first statement is a declaration of a variable for the return code as well as a variable for each parameter. The next statement then assigns default values to each variable. The EXECUTE statement invokes the stored procedure, and finally a SELECT statement returns the return code. The SELECTstatement allows you to verify the return code, because the test script returns it as its own resultset.

Functions

For a scalar-valued function such as dbo.GetZipCode, the generated test script would resemble the following:

-- db unit test for dbo.GetZipCode

DECLARE @RC VARCHAR (5),

   @City VARCHAR (100),

   @StateCode VARCHAR (2)

SELECT @RC = NULL,

   @City = NULL,

   @StateCode = NULL

SELECT @RC = [dbo].[GetZipCode]( @City, @StateCode)

SELECT RC=@RC

Similarly to stored procedures, you declare and assign variables, the function is executed, and the results are selected.

A test script for a table-valued function such as dbo.GetAllZipCodes would resemble the following:

-- db unit test for dbo.GetAllZipCodes

DECLARE @state CHAR (2)

SELECT @state = NULL

SELECT * FROM [dbo].[GetAllZipCodes]( @state)

Triggers

The generated script for a trigger depends on the type of event or events on which the trigger acts (insert, update, or delete). The following script is for a trigger that is defined on the Customers table. This trigger acts on all of the previously mentioned events.

-- db unit test for dbo.TriggerCustomers

DECLARE @CustomerID NCHAR (5),

   @CompanyName NVARCHAR (40),

   @ContactName NVARCHAR (30),

   @ContactTitle NVARCHAR (30),

   @Address NVARCHAR (60),

   @City NVARCHAR (15),

   @Region NVARCHAR (15),

   @PostalCode NVARCHAR (10),

   @Country NVARCHAR (15),

   @Phone NVARCHAR (24),

   @Fax NVARCHAR (24)

SELECT @CustomerID = NULL,

   @CompanyName = NULL,

   @ContactName = NULL,

   @ContactTitle = NULL,

   @Address = NULL,

   @City = NULL,

   @Region = NULL,

   @PostalCode = NULL,

   @Country = NULL,

   @Phone = NULL,

   @Fax = NULL

INSERT INTO [dbo].[Customers] ( CustomerID, CompanyName, ContactName, 

ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax )

   VALUES( @CustomerID, @CompanyName, @ContactName, @ContactTitle, 

@Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax )

--DECLARE @CustomerID NCHAR (5),

--   @CompanyName NVARCHAR (40),

--   @ContactName NVARCHAR (30),

--   @ContactTitle NVARCHAR (30),

--   @Address NVARCHAR (60),

--   @City NVARCHAR (15),

--   @Region NVARCHAR (15),

--   @PostalCode NVARCHAR (10),

--   @Country NVARCHAR (15),

--   @Phone NVARCHAR (24),

--   @Fax NVARCHAR (24)

--

--SELECT @CustomerID = NULL,

--   @CompanyName = NULL,

--   @ContactName = NULL,

--   @ContactTitle = NULL,

--   @Address = NULL,

--   @City = NULL,

--   @Region = NULL,

--   @PostalCode = NULL,

--   @Country = NULL,

--   @Phone = NULL,

--   @Fax = NULL

--

--UPDATE [dbo].[Customers]

--   SET

--      [CustomerID] = @CustomerID,

--      [CompanyName] = @CompanyName,

--      [ContactName] = @ContactName,

--      [ContactTitle] = @ContactTitle,

--      [Address] = @Address,

--      [City] = @City,

--      [Region] = @Region,

--      [PostalCode] = @PostalCode,

--      [Country] = @Country,

--      [Phone] = @Phone,

--      [Fax] = @Fax

--   WHERE

--      [CustomerID] = @CustomerID AND

--      [CompanyName] = @CompanyName AND

--      [ContactName] = @ContactName AND

--      [ContactTitle] = @ContactTitle AND

--      [Address] = @Address AND

--      [City] = @City AND

--      [Region] = @Region AND

--      [PostalCode] = @PostalCode AND

--      [Country] = @Country AND

--      [Phone] = @Phone AND

--      [Fax] = @Fax

--DECLARE @CustomerID NCHAR (5),

--   @CompanyName NVARCHAR (40),

--   @ContactName NVARCHAR (30),

--   @ContactTitle NVARCHAR (30),

--   @Address NVARCHAR (60),

--   @City NVARCHAR (15),

--   @Region NVARCHAR (15),

--   @PostalCode NVARCHAR (10),

--   @Country NVARCHAR (15),

--   @Phone NVARCHAR (24),

--   @Fax NVARCHAR (24)

--

--SELECT @CustomerID = NULL,

--   @CompanyName = NULL,

--   @ContactName = NULL,

--   @ContactTitle = NULL,

--   @Address = NULL,

--   @City = NULL,

--   @Region = NULL,

--   @PostalCode = NULL,

--   @Country = NULL,

--   @Phone = NULL,

--   @Fax = NULL

--

--DELETE FROM [dbo].[Customers]

--   WHERE

--      [CustomerID] = @CustomerID AND

--      [CompanyName] = @CompanyName AND

--      [ContactName] = @ContactName AND

--      [ContactTitle] = @ContactTitle AND

--      [Address] = @Address AND

--      [City] = @City AND

--      [Region] = @Region AND

--      [PostalCode] = @PostalCode AND

--      [Country] = @Country AND

--      [Phone] = @Phone AND

--      [Fax] = @Fax

By default, essentially three test scripts are generated, one for each trigger event (insert, update, and delete). The second two are automatically commented out. At this point, you should decide for which event you want to test and delete the two SQL statement blocks that are not relevant.

As you can see, a SQL statement is generated to perform the appropriate action for each type of trigger event. You must just assign the values for each of the table/view columns that you want to use as parameters to the insert/update/delete statements to complete the test script.

Test Verification

One of the most important aspects of database unit testing is verification. In application unit testing, the answer to verification is assertions. Most unit-testing frameworks come with a collection of assertions that you can use to verify various conditions, such as equality of expected to actual, Boolean value, and so forth.

Team Edition for Database Professionals offers two primary mechanisms for verifying test results.

SQL Assertions

The direct analogy to application unit-testing assertions are SQL assertions that use the RAISERROR command. By using this T-SQL command, you can raise an error that includes an appropriate message and error level. Therefore, you can use this command, with conditional logic, to cause the test to fail when the expected result is not met.

This idea is best explained through an example. Let's write a simple test for the [dbo].[Ten Most Expensive Products] stored procedure in the Northwind database. As you might guess, this stored procedure returns the 10 most expensive products in the Northwind products table. Let's verify in our test that the sproc does, in fact, return 10 rows. You can verify this result by executing the stored procedure and then checking whether the returned row count equals 10. If it does not, you use the RAISERROR command to fail the test.

Such a test script would resemble the following:

EXEC [dbo].[Ten Most Expensive Products]

IF (@@ROWCOUNT <> 10)

  RAISERROR('Ten Most Expensive Products did not return 10 rows',11,1)

This example had rather simple conditional logic; but, of course, you could do any verification logic that you wanted to in T-SQL to verify whether the expected results were, in fact, met.

Test Conditions

Team Edition for Database Professionals, however, does not stop there in terms of test verification. Clearly, users would need a set of commonly performed verification tasks, and the product should offer a better way of performing these. Thus was born the concept of UI-based client-side test conditions that verify the results of your test after the SQL has been executed. You set and configure these conditions inside the Database Unit Test Designer.

Team Edition for Database Professionals includes the test conditions that appear in Table 1.

Table 1. Available test conditions

Test condition	Description
Row Count	Condition fails if ResultSet does not contain expected row count. You can use this condition to do light verification that ensures that the number of rows that you expected was returned in the resultset.
Scalar Value	Condition fails if scalar value in ResultSet does not equal the value that you expected. You can use this condition for deeper verification that ensures that actual values in the returned resultset equal what you expected.
Empty Resultset	Condition fails if the ResultSet is not empty.
Not Empty Resultset	Condition fails if the ResultSet is empty.
Execution Time	Condition fails if the test takes more time than you expected to complete. This duration is the time that it takes to execute the SQL against the server using the ADO.NET provider.
Inconclusive	Condition always leads to an inconclusive result. This condition is added by default to a test. It is useful to have this condition added by default so that, when you run the test, you know that you have not yet completed working on the test. The condition is a marker for work that remains to be done.

The beauty of test conditions inside of Team Edition for Database Professionals is that they are completely customizable. Test-condition customization is an important extensibility point. I thus expect a rich community to form around custom test conditions.

Test Setup and Cleanup

In addition to a test script for each database unit test, it is often very important to include setup and teardown SQL as part of your database unit test. This code ensures that the appropriate environment is set up before the database unit test runs and that the environment is cleaned up immediately after the test. This approach facilitates database unit tests being consistently repeatable—an important attribute of well-written unit tests.

There are several important scenarios here for why you might want to include setup and teardown SQL as part of your unit test. One sample use of setup SQL is to ensure that certain preconditions are met before the test is executed. For example, testing the [dbo].[Ten Most Expensive Products] stored procedure does not make sense if the Products table contains no data. Therefore, you might want to include setup SQL that verifies that the Products table is not empty. In addition, if you are testing a delete stored procedure, you might want to create a row in the relevant table before the test is executed to ensure that the table contains a row for the stored procedure to delete. Similarly, if you are testing an add stored procedure, you might want to delete the row that the stored procedure created in the teardown part of the test script.

Team Edition for Database Professionals makes this possible through the use of pre-test and post-test scripts. Inside the Database Unit Test Designer, you can modify the test script, the pre-test script, or the post-test script. By default, the test script appears. But you can display another script by using the second drop-down list in the Database Unit Test Designer.

Each test script can have associated SQL statements and test conditions. Therefore, in the scenario for the [dbo].[Ten Most Expensive Products] stored procedure, you can imagine having the following pre-test script with a Not Empty ResultSet condition:

SELECT * FROM dbo.Products

In addition to defining individual pre-test and post-test scripts for each database unit test, you can take advantage of the test initialize and test cleanup scripts that run before and after every database unit test in the test class. These scripts are useful for doing common setup and teardown tasks that are relevant for all tests in a given test class. You can access these test scripts by clicking (Common scripts) in the first drop-down list in the Database Unit Test Designer, and then clicking either Test initialize or Test cleanup from the second drop-down list.

Managing Database State

One important consideration in database unit testing is managing the database state. This aspect of database unit testing makes it more difficult than its application-tier counterpart. However, it's fundamental to what database unit testing is, because in a nutshell a database is nothing more than a collection of data or state.

So, the primary question here is: How do I guarantee that the data in my database is what I expect it to be when I run my tests?

This question has two primary aspects:

• First, you must ensure that the database has the expected state, before you run a collection of tests.
• Second, you must ensure that the database has the appropriate state between each test in the test run.

You can use any of several techniques to set up the database state initially:

1. Use a data-generation tool to set the database state, before you run your collection of unit tests. This technique is the best practice for managing database state. To support this best practice, Team Edition for Database Professionals includes a rich data-generation tool. You can use this tool to develop a data-generation plan that specifies exactly how you want to generate data for the tables in your database. The plan is set up with smart defaults, but you can completely customize the generation. The tool generates repeatable test data, so that your tests can always expect the same values. This reproducibility is very important for test verification.
2. Restore a database from backup, or attach an existing database. If you already have a set of test data that you would like to use, often an easy approach is to automatically restore a database from a backup before you run your unit tests. Similarly, you could attach an existing database to your server before you run your tests. This approach becomes problematic when you are constantly changing your database schema, because you must regularly update your database backup.
3. Have your tests assume no state and, as part of each pre-test, set up the appropriate state. This technique, although very "pure" from a philosophical perspective, is not very practical. Not only is it expensive to write such tests, but the performance impact of setting up and tearing down the database state for each test can be prohibitive.

The second problem, managing the state between tests, has several techniques of its own:

1. Transactions—The best approach to solving this problem is to wrap your unit tests into transactions, and then abort the transaction after you finish performing your test verification and before you run the next test. This technique ensures that your database tests are transacted and thus return the database to its prior state before each test.
2. Cleaning up state changes in each post-test script—Another technique is to have each test return the database state to its prior condition. This approach is more manual than the previous suggestion. For example, if you are testing a stored procedure dbo.CreateAuction, you would, in the post-test script, delete the auction from the relevant tables to return the database to its previous state.

Data Generation

Let's drill into the data-generation technique for managing your database state. For any serious database development, you need realistic test data to verify your system. You can take any of several common approaches to test data generation:

• Use production data for testing purposes. For this approach, you typically use old production data or you transform production data by replacing actual values with values that are somewhat equivalent. The advantage of this approach is that nothing is more representative than your production data. Of course, the big downside is that you are using production data, which clearly has large privacy implications, especially in today's highly regulated environment.
• Come up with test data from scratch. New development projects often have no production data to mimic. Although this approach is free of all the privacy issues, it is typically time-consuming to come up with test data easily that actually mimics your production environment.

Team Edition for Database Professionals sought to provide a better approach through its data-generation tool. The premise of this tool is centered on the latter approach—that is, building test data from scratch. This technique completely avoids the privacy/regulatory issues of leveraging production data. The power of this tool lies in its ability to efficiently generate test data from scratch that accurately represents your production environment. The tool facilitates this technique in several ways:

• Highly configurable data generators—You can assign a data generator to each table column. Data generators exist for each column's data type. Each generator has a set of properties that you can configure to fully control the parameters of the generated values.
• Additional powerful generators—These tools include a regular expression generator, which generates values that are based on the specified regular expression (for example, phone numbers). You can also use a data-bound generator to pull values for data generation from a known data source.
• Smart default assignment of generators—By default, the appropriate data generator is automatically assigned to each column, based on the column's data type. This way, as soon as you use this tool, you are generating values of the appropriate data type.
• Recognition of database constraints—The tool understands some of your database constraints and automatically configures the assigned generator to conform to these constraints. For example, if your column has defined on it a check constraint that specifies that values in a column must be larger than some value, the generator will be configured such that the minimum value for data generation will be larger than that value.
• Support for data distributions—You can also apply a distribution on a data generator to generate values according to the distribution. For example, you can generate values according to a normal curve or an exponential curve.
• Generator and distribution extensibility—You can extend the library of generators and distributions to include any kind of data generation that would be appropriate for your test data needs.
• Data generation is repeatable—The values that are generated are based on a seed value, which ensures that the data generated is repeatable. This requirement makes it ideal for use with database unit testing.
• Enforcement of table ratios—You can model certain relationships between your tables and enforce specified ratios between them. For example, let's say that you had an Auctions and Bids table in your database. You can set up a 1:25 ratio between the two tables such that, for every auction generated, 25 associated bids are also generated.

Test Execution

Equally important as test authoring is the test-execution experience. This framework provides myriad means to execute your database unit tests, as well as extensive configurability in how database unit tests are executed. Let's explore each of these dimensions.

The Many Ways to Execute Tests

People are often unaware of the many ways in which they can execute database unit tests by using this framework. Studying these sections should help those who are trying to implement quickly and efficiently the development/test cycle that they should be doing with every change that they make to their schema objects.

Commands on the Top-Level Test Menu

To execute tests, you can open the Test menu and click either of the following commands:

• Start Selected Test Project with Debugger (Shift+Alt+X)
• Start Selected Test Project without Debugger (Ctrl+Shift+X)

To use these commands, the active project in Solution Explorer must contain the tests that you want to run. These commands execute all the tests that are inside the test project that is active.

Test View Window

You can use the Test View window to run specific tests quickly. To bring up the Test View window, open the Test menu, point to Windows, and click Test View. A tool window appears that you can dock alongside Solution Explorer, for example. Then, you just select the check boxes for the tests that you want to run, right-click, and click Run Selection. The Test View window also has simple filtering capabilities, so that you can find a specific test easily.

Test Manager Window

You can also use the Test Manager window to execute database unit tests. This window provides a more sophisticated view of your tests and some capabilities for managing test cases. To open the Test Manager window, open the Test menu, point to Windows, and click Test Manager. The Test Manager window offers the same capabilities of the Test View window and more. You can select check boxes for a set of tests that you want to run and execute them. More importantly, you can define a test list and execute it as a single unit. This capability is very useful for setting up check-in test suites that you ask your developers to execute before they check in any changes.

Test Results Window

The Test Results window appears whenever you execute tests, and it reports the status of those tests. If a test has failed, you can easily run the test again from the Test Results window. You just click the test and then click Run on the toolbar.

F5

By using F5, you can make the process of updating the database and the cycle of building, deploying, generating test data, and rerunning tests very simple.

To set up this process, make the test project the startup project, right-click it, and click Set as Startup Project. On the Tools menu, click Options. In the Optionsdialog box, expand the Projects and Solutions node, click Build and Run, and select the Only build startup projects and dependencies on Run check box. Then, open the Test menu, click Database Test Configuration, and ensure that you have configured automatic deployment and generation of test data.

Now, whenever you change your database schema, just click the test project in Solution Explorer, and press F5. The database project will be built and deployed, test data will be generated, and your tests will be rerun. This approach makes iterative development and testing very simple.

Command-Line

Because database unit tests are first-class test types in Team Edition for Software Testers, you can take full advantage of the MSTest.exe command-line tool for running and automating the execution of your database unit tests.

First, you must ensure that MSTest.exe is in your path, and then open a Command Prompt window. The easiest way to open a Command Prompt window is to open the Start menu, point to Microsoft Visual Studio 2005, and then click Visual Studio Tools.

Then, you must decide what scope of tests you would like to run from the command line. If you use MSTest, you can choose any of the following:

• Test list in a solution

Running a given list of tests in a solution is the recommended approach to running a collection of tests. You can define the list of tests inside the Test Manager window. The Test Manager window is available only if you have Team Edition for Software Testers along with Team Edition for Database Professionals or the entire Team Suite.

After you define your test list, you can run it from the command line by specifying the test metadata file and the name of the test list. The test metadata file is automatically generated inside any solution that contains tests. This file contains the details of the test lists that have been defined.

The command line to run my test list—which is named SprocTests—in my solution—which is named SachNorthwind—would resemble the following:

(I assume here that your current path is at the solution-folder level.)

MSTest /testmetadata:SachNorthwind.vsmdi /testlist:SprocTests

• Entire test project

You can also run all the tests that a test project contains. To take this approach, you must specify the compiled .dll file that is generated for your test project.

Such a specification would resemble the following:

MSTest 

/testcontainer:SachNorthwindTests\bin\debug\SachNorthwindTests.dll

• Individual test

To run an individual test in a test project, you can specify either of the following command lines:

MSTest /testmetadata:SachNorthwind.vsmdi /test:CustOrderHistTest

MSTest 

/testcontainer:SachNorthwindTests\bin\debug\SachNorthwindTests.dll 

/test:CustOrderHistTest

Connection Strings

Team Edition for Database Professionals supports up to two target connection strings for executing database unit tests. You can configure these connection strings in the Database Test Configuration dialog box, which you can open from the Test menu.

The first connection string is the execution connection string, which is used to execute the test script of your database unit test. This connection string should have the same credentials that you would expect your users to have. This characteristic is important for ensuring that the appropriate permissions have been applied in your database.

You can also configure a second connection string with additional permissions for validating test results. By using this string to validate your unit tests, you have full access to your database. A common scenario here is when you are testing, for example, an AddEmployee stored procedure. In many organizations, the average user is expected to perform CRUD operations by using stored procedures and is not expected to have access to the underlying tables. Such stored-procedure invocations should occur with those appropriately restricted privileges. However, it would be useful to verify the results by using an account that has additional permissions to verify whether a row was, in fact, added to the Employees table. To verify this result, you would use your post-test script, which uses the validation connection string. The pre-test script, the Test initialize script, and the Test cleanup script also use this connection.

If you use the automatic deployment and data-generation capabilities, you will use the validation connection because these operations typically require more permissions than what your typical user has.

The validation connection string is optional. If you do not specify a validation connection string, the execution connection string is used in all cases. This approach works only if you are using Microsoft SQL Server authentication. Windows Authentication would result in essentially the same credentials being used for both connections.

Under the Hood of Database Unit Testing

Throughout this paper, we have made extensive use of the Database Unit Test Designer for manipulating our database unit tests. I wanted briefly to take you a little deeper and show you exactly how the framework works. With this knowledge, you can customize your tests even further to increase your power over them.

By using the Database Unit Test Designer, you can create, rename, and delete test methods. For each test method, you can specify the test script SQL (which can be auto-generated for you), as well as the set of test conditions that you want to apply to this specific test. You can easily configure the test conditions in the Property window.

Under the hood, this designer behaves much like the WinForms Designer, which you can use to create Windows Forms applications. The designer generates and round-trips C# and Visual Basic code. You can, therefore, avoid worrying about the underlying code and just manipulate your tests from a design surface that is simpler and more visual. However, you sometimes will want to review the generated code and maybe even modify it. Let's take a look at what code is actually generated.

In the following segment, you can see that the code that is generated resembles regular unit tests that you would author by using Team Edition for Software Testers. The class has the appropriate TestClass attribute, and the method has the appropriate TestMethod attribute.

namespace NorthwindTests

{

    [TestClass()]

    public class StoredProcedureTests : DatabaseTestClass

    {

        [TestMethod()]

        public void Ten_Most_Expensive_Products()

        {

The following code is generated for a given test method:

[TestMethod()]

public void Ten_Most_Expensive_Products()

{

    DatabaseTestActions testActions = 

this.Ten_Most_Expensive_ProductsData;

    // Execute the pre-test script

    //

    System.Diagnostics.Trace.WriteLineIf((testActions.PretestAction != 

null), "Executing pre-test script...");

    ExecutionResult[] pretestResults = 

TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, 

testActions.PretestAction);

    // Execute the test script

    //

    System.Diagnostics.Trace.WriteLineIf((testActions.TestAction != null), 

"Executing test script...");

    ExecutionResult[] testResults = 

TestService.Execute(this.ExecutionContext, this.PrivilegedContext, 

testActions.TestAction);

    // Execute the post-test script

    //

    System.Diagnostics.Trace.WriteLineIf((testActions.PosttestAction != 

null), "Executing post-test script...");

    ExecutionResult[] posttestResults = 

TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, 

testActions.PosttestAction);

}

The following sample is the associated code for that test method and is found in the InitializeComponent() method of the class.

private void InitializeComponent()

{

    Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.DatabaseTestAction 

Ten_Most_Expensive_Products_TestAction;

    System.ComponentModel.ComponentResourceManager resources = new 

System.ComponentModel.ComponentResourceManager(typeof(StoredProcedureTests));

Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Conditions

.RowCountCondition rowCountCondition1;

    this.Ten_Most_Expensive_ProductsData = new 

Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.DatabaseTestActions();

    Ten_Most_Expensive_Products_TestAction = new 

Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.DatabaseTestAction();

    rowCountCondition1 = new 

Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Conditions

.RowCountCondition();

    //

    // Ten_Most_Expensive_ProductsData

    //

    this.Ten_Most_Expensive_ProductsData.PosttestAction = null;

    this.Ten_Most_Expensive_ProductsData.PretestAction = null;

    this.Ten_Most_Expensive_ProductsData.TestAction = 

Ten_Most_Expensive_Products_TestAction;

    //

    // Ten_Most_Expensive_Products_TestAction

    //

    Ten_Most_Expensive_Products_TestAction.Conditions.Add(rowCountCondition1);

    resources.ApplyResources(Ten_Most_Expensive_Products_TestAction, 

"Ten_Most_Expensive_Products_TestAction");

    //

    // rowCountCondition1

    //

    rowCountCondition1.Enabled = true;

    rowCountCondition1.Name = "rowCountCondition1";

    rowCountCondition1.ResultSet = 1;

    rowCountCondition1.RowCount = 10;

}

The InitializeComponent code stores to local variables your test SQL, as well as the test conditions and associated properties that you have applied to the specific test. The actual SQL is stored in the resource file under the node for the .cs or .vb file for the unit test. The code in the test method executes your SQL using ADO.NET and then executes each of the test conditions that you have defined. In addition, the generated code uses various settings saved to the app.config file. For example, the connection strings are pulled from the app.config file.

This sample should give you a sense of what is actually happening when you author a database unit test. Because the generated code is exposed directly to you, this opens a world of possibility in terms of customization. Take a moment now to walk through the generated code and see whether you can piece together what each statement is doing.