Unit Testing SQL Server Objects in Visual Studio 2010
January 21, 2011
Money, time and effort can be saved by finding bugs sooner rather than later. Database unit testing has not enjoyed the same level of unit testing that application development has; however, an increase in tools has begun to change that. Deanna Dicken provides a look at database unit testing development within Visual Studio 2010.
Unit testing in application development is a fairly standard practice whether manual or automated. Most understand how much time, effort, and money can be saved by finding bugs sooner rather than later. In addition, if those tests are repeatable and/or automated, they can spare you much headache and heartache when regression testing needs to be performed.
Database unit testing (for one reason or another) has traditionally not enjoyed the same level or unit testing that application development has in the past. However, an increase in tools and ease of database unit test development has begun to change that. In this article, we’ll take an introductory look at database unit test development within Visual Studio 2010. Specifically, this article’s samples target a SQL Server 2008 R2 database.
Creating a Database Unit Test
Visual Studio 2010 Ultimate and Premium editions have capabilities for the creation and execution of database unit tests. Should you only have Visual Studio 2010 Professional, you can still execute database unit tests, but you will not be able to create them.
Let’s step through the creation of a simple database unit test against a SQL Server 2008 R2 database object. In this case, we will be testing the results of the execution of a stored procedure. Unit tests could also be written to test User Defined Functions and triggers.
You can create a unit test in Visual Studio without having a database project open. However, if you do first open a database project, the unit tests will be stubbed in particular to the database object selected in that project. So, let’s first create a database project.
Setting up a Database Project
In Visual Studio, select File->New Project. As seen below, you are presented with a New Project dialog. Select SQL Server 2008 Database Project, found under Installed Templates->Database->SQL Server. Give it a name and name your solution. I’m calling mine DBProject and DBSolution respectively. Click OK.
Next we need to populate the project with some objects to test. Copy the code below into your favorite text editor and save the file as script.sql.
CREATE SCHEMA [Clients] AUTHORIZATION [dbo]; GO CREATE TABLE [Clients].[Contacts]( [ContactID] [int] NOT NULL, [RegionID] [tinyint] NULL, [ContactName] [varchar](50) NULL, [UpdaterID] [varchar](20) NOT NULL, [UpdateDTM] [varchar](20) NOT NULL, CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED ( [ContactID] ASC) ) GO CREATE PROCEDURE [Clients].usp_GetClientsByRegion ( @RegionID TINYINT ) AS BEGIN SELECT ContactName, RegionID FROM [Clients].[Contacts] WHERE RegionID = @RegionID END GO
Visual Studio allows you to import scripts into your database project to create your objects within the project. Import the script you just saved by right-clicking on your project and selecting Import Script.
On the Import SQL Script File screen, choose Select File from the left (or click next) and Browse on the right to find the file you just saved.
Now examine your project under Schema Objects -> Schemas. You should see a schema named Clients. Under the schema, expand Programmability and then expand Stored Procedures. The stored procedure usp_GetClientsByRegion should show up here.
Deploying the Project
Next, we have to deploy the project to be able to test the object. Right-click the database project in Solution Explorer and select Properties. On the left side of the Properties window, choose Deploy. First change the Deploy action to “Create a deployment script (.sql) and deploy to the database.” Next we’ll need to setup a database connection, so choose Edit… next to the Target Connection field, as seen below.
For the Connection Properties, you’ll need to fill in your database server name, authentication, and database name. I’m using my local server, Windows Authorization, and the database name is DBTest. Test the connection by clicking the Test Connection button in the bottom left. If successful, click OK.
Now we’re ready to deploy the project. Save the project and build it by right-clicking the project in Solution Explorer and choosing Build. You can view the success or failure of the deployment in the Output window.
Creating the Unit Test
Now that we have an object to test in the database, let’s write that unit test. On the menu, click View -> Database Schema View. In the Database Schema View expand Schemas, then Clients, then Programmability, and finally Stored Procedures. Right-click usp_GetClientsByRegion and select Create Unit Tests… from the popup menu.
On the Create Unit Test dialog, name your project and your class. Then click OK.
You should see your test project get created inside Solution Explorer and the design view of your class will open in the editor. On top of all that, a dialog will open asking you to supply your test projects configuration. You should be able to choose the same server and database name as those specified for the database project by selecting it from the dropdown. If for some reason it is unavailable, hit New Connection and fill in as needed.
Now it’s time to choose our test. There is already one lingering there as an Inconclusive test. Let’s delete that one using the red X next to Test Conditions in the middle of the screen. From the Test Conditions dropdown, select Non Empty ResultSet and press the green plus button.
Executing the Unit Test
Save All and let’s give it a shot. Now we expect this test to fail because at no time have we entered any data into the table the stored procedure retrieves from and therefore, the result set will be empty.
To view the list of tests that can be run, on the menu go to Test -> Windows -> Test List Editor. The Test List Editor opens displaying the associated tests. Check the test and hit the Run Checked Tests icon on the toolbar for the editor. The output displays in the Test Results window. And as expected, the unit test for the stored procedure fails.
To make the unit test return success, we need to get some data into the underlying table and also supply the stored procedure call with a parameter that will cause data to be returned. To fill the table with data prior to the test, go back to the tab containing the design view of your test class (or double-click the test in the solution explorer). Add the following insert statements prior to the SELECT in the test class.
INSERT INTO [Clients].[Contacts] VALUES (1, 12, 'Joe Denton', 'abaxter', '1/1/2011'); INSERT INTO [Clients].[Contacts] VALUES (2, 10, 'Freddy Jones', 'hwhite', '9/5/2009'); INSERT INTO [Clients].[Contacts] VALUES (3, 10, 'Jon Dixon', 'kevert', '1/4/2011'); INSERT INTO [Clients].[Contacts] VALUES (4, 11, 'Jack Ford', 'jkitry', '5/8/2010');
We also need to change the parameter to a valid value for results, so in the SELECT that sets @RegionID, change NULL to 10 as seen below.
Note that without truncating the table, deleting the data, changing the INSERTs to MERGEs, or in some way cleaning up the data added for the test, it will not run a second time successfully. Therefore, I’ve added the following line of code to the very end of the script.
DELETE FROM [Clients].[Contacts]
Save All and go back to the Test List Editor and re-run the test. You should now see Passed in the Test Results pane.
Creating and automating the execution of database unit tests can save DBAs and database developers a tremendous amount of time and effort verifying new or modified database objects prior to the hand-off to Quality Assurance. Visual Studio 2010 provides an efficient and user-friendly means to develop, organize, and run database unit tests. This article has only scratched the surface of the capabilities provided.
For More InformationIntroducing Visual Studio Team System 2008 Database Unit Testing
Verifying Database Code by Using Unit Tests
Running Database Unit Tests
Database Unit Test Files
Generating Test Data for Databases by Using Data Generators