Walkthrough: Establishing a Baseline for the Isolated Development Environment

In this walkthrough, a database project has been created and put under version control. The database project contains a schema definition that matches that of a production server. Before development work on that database project starts, you want to establish a baseline of quality by setting up database unit tests, defining a data generation plan, generating test data, and running those tests. A test run will build and deploy the database to your isolated development environment, fill the database with generated data, and execute unit tests to report the results.

Tasks illustrated in this walkthrough include:

  • How to customize build settings for your isolated development environment

  • How to build and deploy the database to your isolated development environment

  • How to define a unit test for a stored procedure that the database project contains

  • How to generate test data and build and run the unit tests

  • How to share the unit tests with the development team by putting them under version control as part of the solution

Prerequisites

You must have installed Microsoft Visual Studio Team Edition for Database Professionals and have access to the AdventureWorks sample database for Microsoft SQL Server 2005. In addition, you must have completed the steps in Walkthrough: Creating an Isolated Database Development Environment.

To customize build settings for your isolated development environment

  1. If the AdvWorksSandbox solution is open in Solution Explorer, go to step 4.

  2. On the File menu, point to Open, and click Project/Solution.

    The Open Project dialog box appears.

  3. Click the AdvWorksSandbox solution, and click Open. (By default, this solution is located in My Documents\Visual Studio 2005\Projects\AdvWorksSandbox.)

    The AdvWorksSandbox solution opens in Solution Explorer.

  4. In Solution Explorer, click the AdvWorksSandbox node.

  5. On the Project menu, click AdvWorksSandbox Properties.

  6. Click the Build tab.

  7. Click Edit next to Target connection.

    The Connection Properties dialog box appears.

    Here you can customize the connection string for your local isolated development environment. The recommended process is that each database developer works in their own copy of the database, which allows them to make changes without adversely affecting the rest of the team. When the developer has tested the changes and is ready to share them with the team, they share the changes through the central version control system. In Walkthrough: Creating an Isolated Database Development Environment, the Target database name was set, but the connection was not.

  8. Type or click the name of the server that you want to use for your isolated development environment. For example, to use your local instance, type (local).

  9. Click the type of authentication that you want to use to connect to the database server.

    By default, Windows Authentication is specified.

  10. Click OK.

    The Target connection displays the connection information.

  11. On the File menu, click Save Selected Items to save your changes to the database project properties.

To build and deploy the database to your isolated development environment

  1. On the Build menu, click Build Solution.

    Your database project builds and the results appear in the Output window.

  2. In Solution Explorer, click the AdvWorksSandbox node.

  3. On the Build menu, click Deploy ProjectName. You can also right-click the AdvWorksSandbox node, and click Deploy.

    The database project builds, generates a build script, and then deploys that script to the isolated database development server that you specified in the database project properties. Next you will generate test data for your isolated development environment.

Define Unit Tests

Typically you would define unit tests for all stored procedures, functions, and triggers. In this walkthrough, you define only one unit test as an example. You define a test for the ufnLeadingZeros function and the dbo.uspGetManagerEmployees stored procedure.

The ufnLeadingZeros function takes an integer and returns a VARCHAR(8) string representation of that integer padded with leading zeros. To test this function, you can pass in a value and test that you get the result that you expect.

The uspGetManagerEmployees stored procedure is passed an employee identifier and returns all the employees that report through the manager whose identifier you specified (the full hierarchy). Because the data generator will produce the same test data if given the same seed value, you can know how many rows should be returned. This test is very simple. In a real project, you would want to create more detailed tests to verify that the correct employees were returned.

To create unit tests

  1. On the View menu, click Schema View.

    Schema View appears if it was not already displayed.

  2. In Schema View, right-click the Stored Procedures folder, and click Create Unit Tests.

    The Create Unit Tests dialog box appears.

  3. Expand the Current selection tree, and select the check boxes for the stored procedure dbo.uspGetManagerEmployees and the function dbo.ufnLeadingZeros. Clear the check boxes for any other stored procedures or functions that are selected.

    Note

    To establish a real baseline for your project, you would create unit tests for all functions, stored procedures, and triggers in your database. This walkthrough is focusing on one stored procedure and one function to demonstrate the process.

  4. In Project, click "Create a new Visual C# test project…".

  5. In New project name, type AWSandboxTestProject, and click OK.

    The unit test project is created, and the Project Configuration dialog box for unit tests appears.

  6. In Database connections, click the connection for AdvWorksSandbox.

  7. In Deployment, select the Automatically deploy database project before running tests check box.

  8. In Database project, click AdvWorksSandbox.dbproj.

  9. In Database state, select the Generate test data prior to running unit tests check box.

  10. In the Database state list, click AWGenPlan.dgen.

  11. Select the Clear database prior to generating test data check box if it is not already selected. and click OK.

    The database unit test project is created and added to the solution. The Database Unit Test Designer opens so that you can modify test conditions and author your tests.

    Note

    If you must modify the test configuration, open the Test menu, and click Database Test Configuration. The Project Configuration dialog box will appear, and you can change the configurations.

    Next you will specify test conditions and write the tests.

To define a unit test for the ufnLeadingZeros function

  1. In the Database Unit Test Designer, in the list of tests, click dbo.ufnLeadingZeros.

  2. Verify that the second list displays "Test".

  3. Replace the Transact-SQL (T-SQL) statements in the upper pane of the designer with the following:

    -- db unit test for dbo.ufnLeadingZeros
    DECLARE @RC VARCHAR (8),
    @Value INT
    
    SELECT @RC = NULL,
    @Value = 27
    
    SELECT @RC = [dbo].[ufnLeadingZeros]( @Value)
    
    IF @RC <> '00000027' 
    RAISERROR(N'ufnLeadingZero: expected "00000027" but got %s.', 16, 1, @RC)
    
    SELECT RC=@RC
    
  4. In the Test Conditions pane, click the test condition whose type is Inconclusive, and delete it by clicking Delete Test Condition (x).

  5. Add a row count test condition by clicking Row Count in the list in the Test Conditions pane and then clicking Add Test Condition (+).

  6. In the Properties window, set the Row Count property to 1.

    If you want to force the test to fail, change @Value from 27 to another value.

To define a unit test for the uspGetManagerEmployees stored procedure

  1. In the Database Unit Test Designer, in list of tests, click dbo.uspGetManagerEmployees.

  2. Verify that the second list displays "Test".

    The Transact-SQL (T-SQL) statements in the upper pane of the designer should consist of the following:

    -- db unit test for dbo.uspManagerEmployees
    DECLARE @RC VARCHAR (8),
    @ManagerID INT
    
    SELECT @RC = NULL,
    @ManagerID = 1
    
    EXEC @RC = [dbo].[uspGetManagerEmployees]( @ManagerID )
    
    SELECT RC=@RC
    
  3. In the Test Conditions pane, click the test condition whose type is Inconclusive, and delete it by clicking Delete Test Condition (x).

  4. Add a row count test condition by clicking Row Count in the list in the Test Conditions pane and then clicking Add Test Condition (+).

  5. In the Properties window, set the Row Count property to 11.

    Note

    To determine the parameter value for the stored procedure and the expected outcome, you might use the Transact-SQL (T-SQL) editor and inspect the contents of the [HumanResources].[Employee] table after data is generated. In this case, the employee whose EmployeeID is 1 is at the top of an 11-person hierarchy. Therefore, the stored procedure should return 11 rows of data. As long as you generate the test data with the same seed value, you should get the same data on each run.

    You are now ready to run your unit tests.

Build and Run Unit Tests

Now that you have defined unit tests, you can build and run them.

To build and run the unit tests

  1. In Solution Explorer, right-click the solution node, and click Rebuild Solution.

    This step forces a rebuild of the database project and the unit test project to guarantee that everything is current.

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

    The Test View window lists several tests. The tests named ManualTest1 and TestMethod1 are created by default. The tests named dbo_uspGetManagerEmployeesTest and dbo_ufpLeadingZeroTest are the ones that you created in this walkthrough.

  3. Select the check boxes for both dbo_uspGetManagerEmployeesTest and dbo_ufpLeadingZeroTest, right-click either, and click Run Selection.

  4. View the results in the Test Results window.

    The database project is deployed to your isolated development environment, the test data is generated, and the rests will run and pass.

Check in Pending Changes

Now that you have defined unit tests to detect whether changes break your database, you can check in your tests and share the solution with the team.

To share the solution with the team

  1. On the View menu, point to Other Windows, and click Pending Changes.

    The Pending Changes window appears.

  2. In Comment, type Defined data generation plan and baseline unit tests.

  3. In the Pending Changes window, click Check In on the toolbar.

    The Check In Progress dialog box appears as the database project and the files that it contains are put under version control. The icons in Solution Explorer update to show that the files are checked into version control.

Next Steps

Now that you have checked the solution into version control, each member of the team can work on their assigned tasks. Each person can work in their own isolated database development environment until their changes are ready to be shared. In Walkthrough: Performing Iterative Database Development in an Isolated Environment, you use refactoring to rename database objects in your isolated development environment. You build, deploy, and test the changes before you check them in so that other team members can synchronize to them.

See Also

Concepts

Overview of Generating Data
Overview of Database Unit Testing
An Overview of Database Build and Deployment
Terminology Overview of Team Edition for Database Professionals

Other Resources

Walkthrough: Performing Iterative Database Development in an Isolated Environment
An Overview of Database Project Settings