Walkthrough: Creating and Running a Database Unit Test

Article
06/26/2007

In this walkthrough, you create a database unit test that tests a simple Transact-SQL (T-SQL) script. Before you can create any database unit test, you must have or create a database project for the database that you want to test. In addition, the database must contain data so that the test can return meaningful results.

In the first procedure of this walkthrough, you create a database project and import the schema of the Northwind database. After you create the project, you create a data generation plan to generate data for a column in the Orders table. You then create a project to contain the test, you write the test itself, and you run the test to verify whether the script works as you expect.

Prerequisites

To complete this walkthrough, you must be able to connect to a database server that has the Northwind database installed.

To create a database project

On the File menu, point to New, and click Project.

The New Project dialog box appears.
In Project types, expand the Database Projects node, and click Microsoft SQL Server.
In the Templates list, click SQL Server 2000 Wizard.
In Name, type TestNorthwind.
In the Solution list, choose Create new Solution if it is not already highlighted.
Select the Create directory for solution check box if it is not already selected.
Clear the Add to Source Control check box if it is not already cleared, and click OK.

The New Database Project Wizard appears. Next you use the wizard to configure your database project and import the initial database schema.

To configure your project and import a database schema

Click Next after you read the Welcome page.
Because the Northwind database contains only one schema, click Organize my project by Object Type.

Important

You cannot change a project's organization after you create it.
In Specify the default schema to use for objects you create in your database project, accept the default of dbo.
On the Set Database Options page, accept the default settings, and click Next.

Note

You can change these options after the project has been created. For more information, see How to: Configure Database Projects for Build and Deployment.
On the Import Database Schema page, select the Import existing schema check box.
In Source Database connection, click the connection that corresponds to the server and database that contains your Northwind database. If the connection does not already exist, click New Connection to create it. If you do not specify a connection, the database project will be created, but no schema will be imported.

Note

You can import a database schema later, if the database project does not already contain database objects. For more information, see How to: Import a Database Schema.
Accept the default import options, and click Next.
On the Configure Build/Deploy page, in Target connection, click the Browse button, and specify a connection to the database server where you want to create your isolated development environment.

To create a new local instance of the database, in the Connection Properties dialog box, type (local) under Server name and click Use Windows Authentication. Click OK.
In Target database name, type TestNorthwind if it is not already specified.
In Deployment collation default, click Use the collation of the server.

This step makes the target database use the database project collation.
Clear the Block incremental deployment if data loss might occur check box.

Because the isolated development environment will contain only generated test data, you can allow data loss to occur.
Click Finish to create the database project and import the database.

To examine the resulting project

In Solution Explorer, expand the TestNorthwind node, and expand the Schema Objects child node.
Explore the sub-nodes that are underneath the Schema Objects node in the hierarchy.

Solution Explorer contains the files that define the objects that are in the schema of the database from which you imported them.
From the View menu, click Other Windows and point to Schema View.
In Schema View, expand the TestNorthwind node.
Explore the sub-nodes that are underneath the TestNorthwind node in the hierarchy.

Schema View contains the objects that are defined in the files that appear in Solution Explorer.
In Solution Explorer, click the TestNorthwind node.
On the Project menu, click TestNorthwind Properties.
On the Properties page, click the Build tab.
In Target database name, type TestNorthwind.
On the File menu, click Save Selected Items.
Click the TestNorthwind project in Solution Explorer.
From the Build menu, click Build Solution.
From the Build menu, click Deploy TestNorthwind.

Note

Each developer will specify the connection string to the database server where they want to host his or her copy of the database.

Next you specify a data generation plan to populate the isolated development database with realistic test data.

To create a data generation plan

In Solution Explorer, expand the TestNorthwind node, and click the Data Generation Plans folder.
On the Project menu, click Add New Item.

The Add New Item dialog box appears.
In the Templates list, click Data Generation Plan.
In Name, type TestNW.dgen, and then click Add.

A data generation plan named TestNW is added to the database project and opened in the editor. The database schema is imported into the data generation plan. Next you modify that plan to generate more realistic test data.

To define the data generation plan for the Orders table

Click dbo.Orders in the data generation plan.
On the Data menu, point to Data Generator, and click Column Details.

The Column Details window appears and displays the columns of the Orders table, their data types, the default generator that is associated with each column, and the generator result.
In the Column Details pane, click ShipCity, and set the Generator field to Data Bound Generator.

This step indicates that you want to populate this column with the results of a query.
On the View menu, click Properties Window.
In the Properties window, go to the Generator section. In the Connection Information property, click the connection that corresponds to the database from which you imported the database schema in a previous procedure.

This step specifies the database connection that you want to use to run the query that will generate data for this column.
In the Properties window, in the Generator section, in the Query property, set the query string to SELECT * FROM Orders.

Note

Do not change the seed value. This value is required to generate a specific piece of data from the Northwind database that will be used later in this walkthrough.
In the Column Details pane, in the Generator Output field for the ShipCity, click [OutputTable1].[ShipCity].

This step selects the column in the result set that you want to associate with this column.
Save the data generation plan.

The next step is to create a database unit test that uses this data.

Create a Database Unit Test

To create a database unit test

In Visual Studio, open the Test menu, and click New Test.

The Add New Test dialog box appears.
Click Database Unit Test.
In Test Name, type SampleUnitTest.
Under Add to Test Project, click Create a new Visual C# test project, and click OK.

The New Test Project dialog box appears.
Retain the default name for the new test project, and click Create.

The new test project is added to the existing TestNorthwind solution. The project contains a file that is named SampleUnitTest.cs, which contains the code of a new test class for your database unit tests. The test class is called SampleUnitTest, and it contains a new unit test method called DatabaseTest1 by default. You can retain the name of this test method or change it to a more meaningful name, and you can customize it to return targeted test results. For more information, see How to: Open a Database Unit Test to Edit.

The Project 'TestProject' configuration dialog box appears.
Under Database Connections, select the TestNorthwind database in the first drop-down list. Its name appears in the format <Server>TestNorthwind.dbo. If this name appears, click it, and go to step 9.
If the TestNorthwind database does not appear, click New Connection.

The Connection Properties dialog box appears.
In the Connection Properties dialog box, identify the server that has the TestNorthwind database, and click the type of authentication to use. Under Select or enter a database name, type TestNorthwind, and click OK to close the Connection Properties dialog box.

Note

After you establish a connection to a database, its name appears in the Project 'TestProject' configuration dialog box under Database Connections.
In the Project 'TestProject' configuration dialog box, select the Use a secondary connection to validate unit tests check box.

Note

You have just assigned the same value to both connection strings. In most cases, you would use a connection string that has higher permissions for validation than for execution. For more information, see Overview of Connection Strings and Permissions.
Select the Generate test data before unit tests are run check box.
In the drop-down list, click the TestNW data generation plan and click OK. This data generation plan will be applied to the TestNorthwind database when you run the unit test.

Note

You can also use this dialog box to configure schema deployment. For more information, see How to: Configure Database Unit Test Execution.

When the Project 'TestProject' configuration dialog box closes, you can see the Database Unit Test Designer.

For each test condition that you add, C# or Visual Basic code is created in a source-code file.
Build the test project by right-clicking it in Solution Explorer and clicking Build.
Add another unit test by clicking Add Test (+) in the navigation bar of the Database Unit Test Designer.

The Specify a name for a Database Test dialog box appears.
Retain the default name for the test method, and then click OK. This adds another unit test to the existing project. This unit test is created as a new test method inside the SampleUnitTest class you created earlier.

You are now ready to write your actual test logic.

To write the database unit test

In the navigation bar of the Database Unit Test Designer, click DatabaseTest1 and make sure that Test is selected in the adjacent drop-down list. This will allow you to create the test script for the test action in the DatabaseTest1 unit test.
Add the following T-SQL statements in the T-SQL Editor:
```
SELECT * from Orders where ShipCity = 'Seattle'
```
In the Test Conditions pane, click the test condition whose type is Inconclusive, and delete it by clicking Delete Test Condition (x).
Add a row count test condition by clicking Row Count in the list in the Test Conditions pane and then clicking Add Test Condition (+).
In the Properties window, set the Row Count property to 1.
From the File menu, click Save All.

You are now ready to run your test.

To run the database unit test

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

The Test View window lists two tests. Earlier in this walkthrough, you created the test that is named DatabaseTest1 and added T-SQL statements and a test condition to it. The test that is named DatabaseTest2 is empty.
Right-click DatabaseTest1, and click Run Selection.

Team Edition for Database Professionals uses the privileged context that you specified to connect to the database and apply the data generation plan. Team Edition for Database Professionals then switches to the execution context before running the T-SQL script in the test. Following this, Team Edition for Database Professionals evaluates the results of the T-SQL script against those that you specified in the test condition and displays either a pass or fail result in the Test Results window.
View the result in the Test Results window.

The test passes, which means that running the SELECT statement returns one row.

Note

If you change either the seed value for [Orders][ShipCity] in your data generation plan or the number of rows to generate, this test might fail. If you generate 50 rows of data from Northwind using the seed value of 5, one of the results has Seattle as the value of the ShipCity field.

Next Steps

In a typical project, you would define additional unit tests to verify that all critical database objects are working correctly. When the set of tests is complete, you check those tests into version control to share them with the team.

After you establish a baseline, you can create and modify database objects and then create associated tests to verify whether a change will break existing behavior.