How to unit test SQL Server 2008 database using Visual Studio 2010

[アーティクル]
07/27/2010

In this article I’ll discuss about unit testing SQL Server 2008 Database project using Visual Studio 2010. As Unit tests test the part of the program integration testing becomes easier, moreover unit tests help enhancing, maintaining or extending a solution provided they are well written. This is first part of this series “How to unit test SQL Server 2008 database using Visual Studio 2010”. The next parts of this series are

Unit test SQL Server 2008 - Part 2 – Focused on internals of database unit testing i.e. assemblies

Unit test SQL Server 2008 - Part 3 – Focused on data generation, schema comparison, data comparison and data driven unit tests

There is no use to write an unit test badly as later on maintainability of the solution suffers. It either has to be a good unit test or no test at all. It’s better to eliminate bugs early rather than late and that is where unit tests wins. Secondly they can be fully automated and ideally should be.

Assuming a team is developing a solution and no unit testing is introduced. After 2 weeks they realize that some bugs have been introduced into the system. Now in order to find and fix these the team

needs to track all the changes to find when it got introduced
check for the features that are dependent and fix them
it may happen and to be true happens most of the time that the other features implemented though working fine may break with the fix that will be applied to close these bugs e.g. initial state of the code is S1. Developer A checks in that code and now state is S2. Developer B fixes some bug that was there in S1. As Developer B is working on codebase S2 so after he fixes the bug the state of code is S3. It was reported that there was an bug in changes Developer A made. Now as Developer B has checked in on state S2 and if those depend on Developer A changes that means after Developer A will fix his part, Developer B changes will break. See how much havoc it can create as here I considered only 2 check ins and 2 Developers.

This is where the power of writing good unit tests kick in. This ripple effect of bugs had not happened if unit testing framework was incorporated into the system. This adds to the confidence of the team and confidence can have a positive effect beyond the boundaries of the team. The next point is “Is unit testing a overload” and the answer is “No” unless you write bad unit tests.

“With power comes great responsibility” quote from Spiderman movie seems to be the right answer. I can go deep into unit tests but in this article I’ll discuss how we can unit test SQL Server 2008 database using Visual Studio 2010.

There are templates for SQL Server 2005 database project in Visual Studio 2008/2010 but those are out of scope of this article as I have not tried them. They may or may not have the same workflow as discussed later so I will restrict the scope of this article to only VS 2010 and SQL Server 2008.

Software:

VS 2010 Ultimate

SQL Server 2008

Test Data:

In this example as displayed below I will implement unit test for adding a Student i.e. uspAddStudent store procedure. If unit test has to be written for uspAddEventPass stored procedure then we need to set pre-test conditions as it has foreign key relation to Student and Event tables along with the test condition.

Please find below the SQL script for creating the test database for unit testing. This script will be imported so please save this to file.

 USE [SampleDB]
 GO
 /****** Object:  Table [dbo].[Event]    Script Date: 07/27/2010 06:20:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[Event](
 [EventId] [bigint] IDENTITY(1,1) NOT NULL,
 [EventName] [nchar](100) NOT NULL,
 CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED 
 (
 [EventId] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[Student]    Script Date: 07/27/2010 06:20:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[Student](
 [StudentId] [bigint] IDENTITY(1,1) NOT NULL,
 [StudentName] [nchar](100) NOT NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
 (
 [StudentId] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[EventPass]    Script Date: 07/27/2010 06:20:37 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[EventPass](
 [EventPassId] [bigint] IDENTITY(1,1) NOT NULL,
 [EventId] [bigint] NOT NULL,
 [StudentId] [bigint] NOT NULL,
 CONSTRAINT [PK_EventPass] PRIMARY KEY CLUSTERED 
 (
 [EventPassId] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  StoredProcedure [dbo].[uspAddStudent]    Script Date: 07/27/2010 06:20:38 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author:        <Author,,Name>
 -- Create date: <Create Date,,>
 -- Description:    <Description,,>
 -- =============================================
 CREATE PROCEDURE [dbo].[uspAddStudent]
 -- Add the parameters for the stored procedure here
 @StudentName nchar(100)    
 AS
 BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
  
 -- Insert statements for procedure here
 INSERT INTO [dbo].[Student]
 ([StudentName])
 VALUES(@StudentName)
 END
 GO
 /****** Object:  StoredProcedure [dbo].[uspAddEvent]    Script Date: 07/27/2010 06:20:38 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author:        <Author,,Name>
 -- Create date: <Create Date,,>
 -- Description:    <Description,,>
 -- =============================================
 CREATE PROCEDURE [dbo].[uspAddEvent]
 -- Add the parameters for the stored procedure here
 @EventName nchar(100)    
 AS
 BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
  
 -- Insert statements for procedure here
 INSERT INTO [dbo].[Event]
 ([EventName])
 VALUES(@EventName)
 END
 GO
 /****** Object:  StoredProcedure [dbo].[uspGetEventDetails]    Script Date: 07/27/2010 06:20:38 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author:        <Author,,Name>
 -- Create date: <Create Date,,>
 -- Description:    <Description,,>
 -- =============================================
 CREATE PROCEDURE [dbo].[uspGetEventDetails]
 AS
 BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
  
 -- Insert statements for procedure here
 SELECT EP.[EventPassId],EP.[EventId], E.EventName, EP.[StudentId], S.StudentName      
 FROM [dbo].[EventPass] As EP
 INNER JOIN .dbo.Student As S
 ON EP.StudentId = S.StudentId
 INNER JOIN .dbo.[Event] As E
 ON EP.[EventId] = E.[EventId]
 
 END
 GO
 /****** Object:  StoredProcedure [dbo].[uspAddEventPass]    Script Date: 07/27/2010 06:20:38 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author:        <Author,,Name>
 -- Create date: <Create Date,,>
 -- Description:    <Description,,>
 -- =============================================
 CREATE PROCEDURE [dbo].[uspAddEventPass]
 -- Add the parameters for the stored procedure here
 @EventId bigint,
 @StudentId bigint
 AS
 BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
  
 -- Insert statements for procedure here
 INSERT INTO [dbo].[EventPass]
 ([EventId]
 ,[StudentId])
 VALUES
 (@EventId
 ,@StudentId)
 END
 GO
 /****** Object:  ForeignKey [FK_EventPass_Event]    Script Date: 07/27/2010 06:20:37 ******/
 ALTER TABLE [dbo].[EventPass]  WITH CHECK ADD  CONSTRAINT [FK_EventPass_Event] FOREIGN KEY([EventId])
 REFERENCES [dbo].[Event] ([EventId])
 GO
 ALTER TABLE [dbo].[EventPass] CHECK CONSTRAINT [FK_EventPass_Event]
 GO
 /****** Object:  ForeignKey [FK_EventPass_Student]    Script Date: 07/27/2010 06:20:37 ******/
 ALTER TABLE [dbo].[EventPass]  WITH CHECK ADD  CONSTRAINT [FK_EventPass_Student] FOREIGN KEY([StudentId])
 REFERENCES [dbo].[Student] ([StudentId])
 GO
 ALTER TABLE [dbo].[EventPass] CHECK CONSTRAINT [FK_EventPass_Student]
 GO

Steps:

Step 1:

Open Visual Studio and create a new SQL Server 2008 Database Project as displayed below

Step 2:

Save the test SQL script for on you machine. Right click the project and click import script and import the saved script as displayed below

After import is done you will find the schema objects as highlighted below

Step 3:

In the Properties –> Deploy tab of the project set

Configure deployment settings for –> My Isolated development environment

Deploy Action –> Create a deployment script(.sql) and deploy to database

Target Connection –> Edit/New connection

Deployment configuration file –> Edit –> Uncheck Block incremental deployment if data loss might occur checkbox

as displayed below

Here an isolated development environment is set so that each database developer can make and test changes without affecting other members of the team. The isolated database development environment is based on the database project that contains the definitions for all the objects that are in the production database.

Create a deployment script(.sql) and deploy to database is selected as we want the database project to be deployed for each build configuration.

When you make changes to a database project and deploy them to a database server, some changes can result in data loss from database project being dropped and recreated. Here data loss is allowed as we are working in an isolated development environment on a database that is populated with test data.

Save changes and Build the solution.

Step 4:

Deploy this project as displayed below

You can configure the properties as displayed below. If you click Show All files then you can view the files generated e.g. in Debug mode. The connection string that you specified is saved in sql –> debug –> UnitTestDB.deploymanifest

Step 5:

I’ll now create database unit tests for the stored procedures. As displayed below go to View –> Database Schema View and then right click the stored procedure for which you want to write unit test and click Create Unit Tests as displayed below.

In case this option is disabled then there must be some issue with the sql file that has been imported and check the Error List for Warnings/Errors e.g. in my case I had specified [Database].[Schema].[Object] in one of the store procedure and though there was warning but I ignored as there were no errors :) and when I tried to create the unit test the option was disabled. The fix was to use [Schema].[Object] as displayed below

Now select the procedures as displayed below

Step 6:

In the configuration dialog for project ‘SampleDBTestProject’ configure options as displayed below

The connection string that you specified is saved in the app.config of this project.

Step 7:

Open the SampleDBStoredProcedureUnitTests as displayed below

Select dbo_uspAddStudentTest and in Test Conditions remove Inconclusive test condition. If an Inconclusive Test Condition is added to a database unit test, the test will always fail with an Inconclusive result. It’s not a passed result, nor a failed result. It indicates that the results of the test could not be verified. Add Row Count test condition and set Row Count = 1 as only one row gets added when this store procedure runs as displayed below

The snippet is displayed below

 -- unit test for dbo.uspAddStudent
 DECLARE @RC AS INT, @StudentName AS NVARCHAR (100);
  
 SELECT @RC = 0,
 @StudentName = 'Unit Test';
  
 EXECUTE @RC = [dbo].[uspAddStudent] @StudentName;
  
 SELECT * FROM [dbo].[Student];

Step 8:

Go to Test –> Windows –> Test View and select dbo_uspAddEventTest and Run Selection as displayed below.

In the Test Results the results of the tests run will be displayed. In case a particular test fails it will display as failed and the reason for failure.

In the same way the unit tests for other stored procedures can be written by specifying the Pre-Test/Test/Post-Test Conditions and Test Conditions.

The link to the next part of this series is Unit test SQL Server 2008 - Part 2

del.icio.us Tags: Visual Studio 2010,SQL Server 2008,Unit Test,Test View,Database Schema View

References: https://msdn.microsoft.com/en-us/library/aa833283.aspx

https://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-01-37-36/1351.UnitTestDB.zip

Comments

Anonymous
August 22, 2010
This is the second article I am writing on unit testing SQL Server 2008 database using Visual Studio
Anonymous
January 15, 2012
I'll update it by next week to make it better. Thanks for feedback.
Anonymous
August 02, 2012
Just to clarify, This doesn't work if you don't have VS2010 Ultimate right? I tried it with PRO and the options in the menus are completely different.
Anonymous
August 06, 2012
@Anibal - As per msdn "You can create, modify, and run database unit tests in Visual Studio Premium and Visual Studio Ultimate. In Visual Studio 2010 Professional, you can run database unit tests but you cannot create or modify tests in the designer." Also I'd like to share that SSDT is an evolution of existing VS database project type. You can read more about SSDT @ msdn.microsoft.com/.../hh322942.aspx "Is SSDT a replacement of the Visual Studio for Database Professionals ("Data Dude") Product? SSDT is an evolution of the existing Visual Studio Database project type. You should note that some of the features (e.g., data generation, unit testing, and data compare) will be absent in the first release of SSDT, but the plan is to integrate these features as quickly as possible into SSDT in future releases. Over time, SSDT will subsume the existing Visual Studio SQL Server Database project features. Will Database Projects (“Data Dude”) remain in the next Release of Visual Studio? In the next major Visual Studio release, SSDT will both replace and provide conversion capability for existing Visual Studio database projects. You can preview this change in the Microsoft® Visual Studio® 11 Beta."

次の方法で共有