Unit Testing SQL Server Objects in Visual Studio 2010


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.

Introduction

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. 

Click Finish. 

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.

Conclusion

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 Information


Introducing 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

»


See All Articles by Columnist

Deanna Dicken

Deanna Dicken
Deanna Dicken
Deanna Dicken is an architect with approximately 20 years of IT experience. In that time she has worked on many large-scale, mission-critical applications and has been involved at all phases of the lifecycle. She has also contributed to three SQL Server MCSE books, co-authored Learn SQL in a Weekend, and tech edited many other titles. She lives on the outskirts of Indianapolis, Indiana with her husband, Curtis, and children, Kylee and Zach.

Latest Articles