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 Visual Studio Team System Database Edition and have access to the AdventureWorks2008 sample database for SQL Server 2008. 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
If the AdvWorksSandbox solution is open in Solution Explorer, go to step 4.
On the File menu, point to Open, and click Project/Solution.
The Open Project dialog box appears.
Click the AdvWorksSandbox solution, and click Open. (By default, this solution is located in My Documents\Visual Studio 2008\Projects\AdvWorksSandbox.)
The AdvWorksSandbox solution opens in Solution Explorer.
In Solution Explorer, click the AdvWorksSandbox node.
On the Project menu, click AdvWorksSandbox Properties.
Click the Deploy tab.
In the Configure deployment settings for drop-down list, select My isolated development environment.
Note
In the first introductory walkthrough, you performed a test build and deployment using the settings for the database project. In this walkthrough, you are configuring settings for your isolated development environment. These settings can be different for each developer on your team.
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 updates a private copy of the database so that changes cannot adversely affect the rest of the team. When the developer has tested the changes and is ready to share them with the team, he or she shares 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.
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).
Click the type of authentication that you want to use to connect to the database server.
By default, Windows Authentication is specified.
Click OK.
The Target connection displays the connection information.
In the Deployment configuration file drop-down list, select Properties\Database.sqldeployment.
In the Set the SQL Command variables file drop-down list, select Properties\Database.sqlcmdvars.
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
On the Build menu, click Build Solution.
Your database project builds, and the results appear in the Output window.
In Solution Explorer, click the AdvWorksSandbox node.
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 who 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
On the View menu, click Schema View.
Schema View appears if it was not already displayed.
In Schema View, open the Schemas folder, open the dbo folder, and then open the Programmability folder.
Right-click the Functions folder, and click Create Unit Tests.
The Create Unit Tests dialog box appears.
Expand the Current selection tree, and select the check box for the function dbo.ufnLeadingZeros and for the stored procedure dbo.uspGetManagerEmployees. Clear any check boxes that are selected for any other stored procedures or functions.
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 function to demonstrate the process.
In Project, click Create a new Visual C# test project….
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.
In Database connections, click the connection for AdvWorksSandbox.
In Deployment, clear the Automatically deploy database project before running tests check box.
In Database state, select the Generate test data prior to running unit tests check box.
In the Database state list, click AWGenPlan.dgen.
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 test.
To define a unit test for the ufnLeadingZeros function
In the Database Unit Test Designer, in the list of tests, click dbo.ufnLeadingZeros.
Verify that the second list displays "Test".
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=@RCIn 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 click Add Test Condition ().
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.
You are now ready to run your unit tests.
To define a unit test for the uspGetManagerEmployees stored procedure
In the Database Unit Test Designer, in the list of tests, click dbo.uspGetManagerEmployees.
In the second list, verify that "Test" appears.
The Transact-SQL (T-SQL) code in the upper pane of the designer should consist of the following statements:
-- db unit test for dbo.uspManagerEmployees DECLARE @RC VARCHAR (8), @BusinessEntityID INT SELECT @RC = NULL, @BusinessEntityID = 2 EXEC @RC = [dbo].[uspGetManagerEmployees] @BusinessEntityID SELECT RC=@RCIn the Test Conditions pane, click the test condition whose type is Inconclusive, and click Delete Test Condition (x).
The test condition is deleted.
In the Test Conditions pane, click Row Count in the list, and then click Add Test Condition ().
The test condition is added.
In the Properties window, set the Row Count property to 24.
Note
To determine the parameter value for the stored procedure and the expected outcome, you might use the 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
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.
On the Test menu, point to Windows, and then click Test List Editor.
The Test View window lists several tests. The tests that are named ManualTest1 and TestMethod1 are created by default. You created the tests that are named dbo_ufpLeadingZeroTest and dbo_uspGetManagerEmployees earlier in this walkthrough.
Select the check boxes for dbo_ufpLeadingZeroTest and dbo_uspGetManagerEmployees, right-click either test, and click Run Checked Tests.
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 test 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 your team.
To share the solution with the team
On the View menu, point to Other Windows, and click Pending Changes.
The Pending Changes window appears.
In Comment, type Defined data generation plan and baseline unit tests.
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 in to version control.
Next Steps
Now that you have checked the solution in to version control, each member of the team can work on his or her assigned tasks. Each person can work in an isolated database development environment until any changes are ready to be shared.
In Walkthrough: Performing Iterative Database Development in an Isolated Development 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, and then other members of the team can synchronize your changes with their own isolated development environments.
See Also
Tasks
Walkthrough: Performing Iterative Database Development in an Isolated Development Environment
Concepts
Generate Test Data for Databases by Using Data Generators
Verifying Existing Database Code with Unit Tests
Build and Deploy Databases to an Isolated Development Environment