Application development musings
on .NET, Azure, Docker, Kubernetes & OSS
Please find the slides and demos for the session I presented at Microsoft User Group Hyderabad(MUGH). The agenda of the session was
Download from
This is the last article of the series “How to unit test SQL Server 2008 database using Visual Studio 2010”. The previous articles are
Unit test SQL Server 2008 – Part 1 – Focused on setting up environment, creating database project and executing simple unit tests
Unit test SQL Server 2008 – Part 2 – Focused on internals of database unit testing i.e. assemblies
I’ll divide this article into two logical parts
Test data is created for verifying the behaviour of database objects in a database project. Generating data that is appropriate for the schema of the database but unrelated to the production data helps protect the privacy or security of the production data. Data generation plan can be added to the database project as displayed below
When data generation plan is created for a database project, the plan is based on the schema of the project. If after creation of the plan schema changes, Visual Studio prompts to update the plan.
There are two types of templates available for creating data generation plan
Select the template i.e. Data Generation Plan and specify name and Add as displayed below
After you click Add you can customize various aspects of data generation plan in the Data generation plan designer as displayed below
Here three tables are displayed as I had three tables Event, EventPass and Student in my database project. The Related Table shows a dropdown only for EventPass table because EventPass has foreign key relation to Event and Student table. Please refer to first article for details.
You can choose Table and Rows to Insert in each table for all the tables provided for table that has related tables e.g. EventPass Related Table is set to None. If Related Table is chosen then you can specify the Ratio to Related Table and depending upon this ratio the Rows to Insert column will be populated.
You can preview as well as change the generator as displayed below
Once you are done configuring this plan go to Data –> Data Generator –> Generate Data as displayed below
Specify a new or an existing connection in the Connect to Database dialog that will appear next. Please note that you need to have the same schema in the database that you have specified for this else Data generation will fail. In this example my database is SampleDBTestData and it contains tables only. Once data generation is completed you can see the generate data in the database that you specified.
Select the Data transform plan template, specify name and Add as displayed below
I have highlighted the sections that can be configured in this dialog. Rest of the steps are same as discussed above i.e. once you are done configuring this plan go to Data –> Data Genarator –> Generate Data.
Schema comparison is a powerful tool that is used to compare database schemas as displayed below
The result when source and target schema are same is displayed below
The result when source and target schema were somewhat different is displayed below
The data from two databases can be compared as display below
This concludes the first logical part.
In order to write a data driven unit test two important characteristics that we use are
As displayed in code snippet I have marked the dbo_AddStudentTest with DataSource and TestMethod attributes and specified the provider type, connection string, table name and data access method as displayed below
[DataSource("System.Data.SqlClient", @"Data Source=SQLSERVER2008;Initial Catalog=TestData;Persist Security Info=True;User ID=sa;Password=*****;Pooling=False", "EmployeeData", DataAccessMethod.Sequential), TestMethod()]
Inside the test method I can retrieve that parameter value that I am expecting from Test Data from the TestContext as displayed below
object parameterValue = TestContext.DataRow.ItemArray.GetValue(0);
The unit test script is expects the parameter “StudentName” which I’ll populate from the test data. The same example I provided in first article of this series I had declared the parameter in the script and hard coded value for that parameter inside the script. Now you can drive unit tests from the test data. The unit test script for this test is displayed below.
-- unit test for dbo.uspAddStudent
DECLARE @RC AS INT
SELECT @RC = 0
EXECUTE @RC = [dbo].[uspAddStudent] @StudentName;
SELECT * FROM [dbo].[Student];
The code snippet is displayed below
[DataSource("System.Data.SqlClient", @"Data Source=SQLSERVER2008;Initial Catalog=TestData;Persist Security Info=True;User ID=sa;Password=*******;Pooling=False", "EmployeeData", DataAccessMethod.Sequential), TestMethod()]
public void dbo_uspAddStudentTest()
{
DatabaseTestActions testActions = this.dbo_uspAddStudentTestData;
// Execute the pre-test script
//
System.Diagnostics.Trace.WriteLineIf((testActions.PretestAction != null), "Executing pre-test script...");
ExecutionResult[] pretestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PretestAction);
//Specifying the value of parameter from TestContext
object parameterValue = TestContext.DataRow.ItemArray.GetValue(0);
DbParameter parameter= CreateDBParameter("StudentName", ParameterDirection.Input, DbType.String, 100, parameterValue);
// Execute the test script
//
System.Diagnostics.Trace.WriteLineIf((testActions.TestAction != null), "Executing test script...");
ExecutionResult[] testResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.TestAction, parameter);
// Execute the post-test script
//
System.Diagnostics.Trace.WriteLineIf((testActions.PosttestAction != null), "Executing post-test script...");
ExecutionResult[] posttestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PosttestAction);
}
private DbParameter CreateDBParameter(string name, ParameterDirection direction, DbType dbType, int size, object value)
{
DbParameter parameter = base.ExecutionContext.Provider.CreateParameter();
parameter.Direction = direction;
parameter.DbType = DbType.String;
parameter.ParameterName = name;
parameter.Size = size;
parameter.Value = value;
return parameter;
}
One improvement that I can make is instead of hard coding the provider, connection string etc. by adding them to the App.Config as displayed below
<configSections>
<section name="microsoft.visualstudio.testtools" type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
</configSections>
<connectionStrings>
<add name="sqlDataCon" connectionString="Data Source=SQLSERVER2008;Initial Catalog=TestData;Persist Security Info=True;User ID=sa;Password=*********;Pooling=False" providerName ="System.Data.SqlClient"/>
</connectionStrings>
<microsoft.visualstudio.testtools>
<dataSources>
<add name ="sqlDataSource" connectionString="sqlDataCon" dataTableName="EmployeeData" dataAccessMethod="Sequential"/>
</dataSources>
</microsoft.visualstudio.testtools>
and mark the Test method as displayed below
[DataSource("sqlDataSource")]
[TestMethod]
public void dbo_uspAddStudentTest()
{
}
You can specify Common scripts(common to all tests), Pre and Post scripts to a test in test editor. By default these are optional. The order of execution of these scripts is
The context of these are also different i.e. TestInitializion, Pre Test script, Post Test script, TestCleanup execute having PrivelegedContext whereas Test script executes having ExecutionContext.
I have discussed the test conditions in previous articles. In this article I’ll show two properties i.e. Configuration and Enabled. Enabled exist across all test conditions. You can enable/disable a test condition using this. Configuration property exists for Expected Schema and Data Checksum test conditions. For these conditions you need to configure the result you are expecting by clicking the button as highlighted below
You can define a custom test condition to verify the behaviour of a database object in ways that the built-in conditions do not support. Please read this msdn article for more details Define Custom Conditions for Database Unit Tests
There are three ways through which we can specify transactions as listed below
base.ExecutionContext.Connection.BeginTransaction()
This only works if there are no pre and post test scripts for an unit test. This is because Pre and Post scripts are executed having PrivelegedContext where as Test script is executed having ExecutionContext
using (TransactionScope scope = new System.Transactions.TransactionScope(TransactionScopeOption.Required))(base.PrivilegedContext.Connection.EnlistTransaction(Transaction.Current);base.ExecutionContext.Connection.EnlistTransaction(Transaction.Current);//Rest of the test method code}
In this case we have to enlist the transaction as we have two different contexts i.e. PrivelegedContext and ExecutionContext. This means if these we need to have distributed transaction coordinator service running as we are having transaction across two different connections.
This completes the series “How to unit test SQL Server database using Visual Studio 2010”. Please share the feedback so that I can improve/rectify the series.
This is the second article I am writing on unit testing SQL Server 2008 database using Visual Studio 2010. The other articles are
Unit test SQL Server 2008 – Part 1 – Focused on setting up environment, creating database project and executing simple unit tests
Unit test SQL Server 2008 – Part 3 – Focused on data generation, schema comparison, data comparison and data driven unit tests
In this article I will explore the important features that can be configured to build comprehensive database unit tests. The two assemblies that are the backbone for database unit testing are
In this article I’ll dig deep into these two assemblies and explore the various components of these assemblies that are used to execute unit test for a database.
I’ll start with the Database Unit Test Designer and then move on to explaining various components.
I have highlighted the various options available in Database Unit Test Designer and added short description as displayed below
The scripts that are created are stored in the resource file.
I’ll now discuss the important components of the Unit Testing Ecosystem which reside inside the assemblies
This assembly is the testing framework assembly that the unit test engine depends upon while executing unit tests.
This namespace supplies classes that provide unit testing support. This namespace contains many attributes that identify test information to the test engine regarding data sources, order of method execution, program management, agent/host information, and deployment data.
I will touch upon the important attributes and classes that are required by the test engine to execute unit tests.
The order in which methods marked with these attributes will run by the test engine is displayed below assuming we have one assembly, one class marked with TestClassAttribute, one method marked with TestMethodAttribute. I have provided this example as
I have discussed the common attributes required by the test engine to execute unit test. For more information about this assembly please read this link microsoft.visualstudio.testtools.unittesting. I’ll now move on to the next assembly i.e. Microsoft.Data.Schema.UnitTesting.
This assembly provides the classes to run and execute unit tests. The classes grouped by namespace for this assembly are displayed below
This namespace provides classes that you can use to create and execute database unit tests. The classes are listed below
This namespace provides classes used to read or write configuration information to the DatabaseUnitTesting section of the app.config. The classes are listed below
This namespace provides classes that you can use to create and manage test conditions. The classes are listed below
This concludes the part-2 of the series “How to unit test SQL Server 2008 using Visual Studio 2010”.
In the next article I’ll show how to unit test a database using the components discussed in this article.
The link to next article of this series is Unit test database – Part 3
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 – Focuses on internals of database unit testing i.e. assemblies
Unit test SQL Server 2008 – Part 3 – Focuses 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
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.
VS 2010 Ultimate
SQL Server 2008
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
Open Visual Studio and create a new SQL Server 2008 Database Project as displayed below
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
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.
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
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
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.
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];
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
References: http://msdn.microsoft.com/en-us/library/aa833283.aspx