Unit Test Archives -

January 17, 2012January 22, 2019

How to use Pex and Moles to generate unit tests for a project having external dependency(WCF Proxy) using Visual Studio 2010 SP1

Source Code:

In this post I’ll discuss about writing unit tests using Pex and Moles. Pex and Moles are Visual Studio 2010 Power Tools that help Unit Testing .NET applications.

Pex automatically generates test suites with high code coverage. Right from the Visual Studio code editor, Pex finds interesting input-output values of your methods, which you can save as a small test suite with high code coverage.
Moles allows to replace any .NET method with a delegate. Moles supports unit testing by providing isolation by way of detours and stubs i.e. Generate a Mole type from the original type and redefine its behavior using delegates.

Pex and Moles can be downloaded from http://research.microsoft.com/en-us/projects/pex/.

I’ll explain the steps to generate unit tests for a project which calls a WCF service. Pex will be used to generate unit tests. Moles will be generated to isolate the external dependency(WCF proxy) and behavior will be redefined using delegates.

The projects inside the sample solution are

DemoService: This project is a WCF Service.
DemoLibrary: This project is a Class library and service reference to DemoService has been added. Unit tests will be generated for this project.
ConsoleApp: This project is a Console application.
DemoLibrary.Tests: This is a Test project and contains unit tests for DemoLibrary.

The solution structure is displayed below

DemoLibrary calls DemoService though proxy as displayed in the Layer diagram

I’ll now discuss in brief the code snippets of each project

WCF Service(DemoService): This service provided only a single operation

[ServiceContract]
public interface IDemoService
{
    [OperationContract]
    string Search(string criteria);
}

WCF Service Client(DemoLibrary): It calls the Search method of DemoService through proxy as displayed below

public string GetResults(string s)
{
    DemoServiceReference.DemoServiceClient client = null;
 
    try
    {
        client = new DemoServiceReference.DemoServiceClient();
        client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;                
        client.ChannelFactory.Credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
        s = client.Search(s);
        return s;
    }
    finally
    {
        if (client != null)
        {
            if (client.State == CommunicationState.Opened)
            {
                client.Close();
            }
            else if (client.State == CommunicationState.Faulted)
            {
                client.Abort();
            }
        }
    }
}

Unit Testing DemoLibrary using Pex and Moles:

In order to generate unit tests for WCF Service Client(DemoLibrary) project the steps are

Right click on the class for which unit tests needs to be generated and click “Create Parameterized Unit Tests” as displayed below
A popup will be displayed where Filters and Output can be modified. Click Ok to go to next step.

A new test project will be created. Test Class and Test stub will be be added to this project as displayed below

/// <summary>This class contains parameterized unit tests for Search</summary>
[PexClass(typeof(Search))]
[PexAllowedExceptionFromTypeUnderTest(typeof(InvalidOperationException))]
[PexAllowedExceptionFromTypeUnderTest(typeof(ArgumentException), AcceptExceptionSubtypes = true)]
[TestClass]
public partial class SearchTest
{
    /// <summary>Test stub for GetResults(String)</summary>
    [PexMethod]
    public string GetResults([PexAssumeUnderTest]Search target, string s)
    {
        string result = target.GetResults1(s);
        return result;
        // TODO: add assertions to method SearchTest.GetResults(Search, String)
    }
}

There is an external dependency(GetResults makes a service call though the WCF Proxy) so “Run Pex Explorations” will not generate unit tests as displayed below
In order to isolate the external dependency we need to generate Moles before running Pex Explorations. Moles will be generated for DemoLibrary and System.ServiceModel assemblies and behavior will be redefined using delegates. There are two ways to generate a mole for an assembly. I’ll show you both the approaches
1. Visual Studio: This is the easiest way.
  1. Right click on the reference and generate Moles for that assembly as displayed below.
  2. A .Moles file will be added to the project. Build the project and Moles.dll will be added to MolesAssemblies folder as displayed below .
2. Command Prompt: Moles can be generated from the command prompt.
  1. Run the moles.exe and specify the assembly path for which Moles needs to be created.
  2. Copy the generated assembly to the Project and add reference to it.
Similarly as explained above we need to generate Moles for System.ServiceModel assembly. For Visual Studio 2010 SP1 this may fail with error message “The type or namespace name ‘IHttpCookieContainerManager’ does not exist in the namespace ‘ssm::System.ServiceModel.Channels’ (are you missing an assembly reference?)”. This step however works fine for Visual Studio 2010. The fix is to exclude the type i.e. ‘IHttpCookieContainerManager’ from StubGeneration as displayed below

The next step is to Mock the Service call(redefine behavior using delegates) and add Asserts as displayed in code snippets below

/// <summary>Test stub for GetResults(String)</summary>
[PexMethod]
public string GetResults([PexAssumeUnderTest]Search target, string s)
{
    MockWCFService<IDemoService>();
 
    MDemoServiceClient.Constructor = (var1) =>
       new MDemoServiceClient { };
 
    MDemoServiceClient.AllInstances.SearchString = (var1, var2) =>
    {
        return "Result";
    };
 
    string result = target.GetResults(s);
    PexAssert.IsNotNullOrEmpty(result);
    PexAssert.AreEqual(result, "Result");
    return result;            
}
 
/// <summary>
/// Mocks the WCF service.
/// </summary>
private void MockWCFService<TService>() where TService : class
{
    MClientCredentials.Constructor = (var1) =>
        new MClientCredentials()
        {
            WindowsGet = () => { return new MWindowsClientCredential(); }
        };
 
    MClientCredentials.AllInstances.WindowsGet = (var1) =>
    {
        return new MWindowsClientCredential();
    };
 
    MWindowsClientCredential.AllInstances.ClientCredentialGet = (var1) =>
    {
        return new System.Net.NetworkCredential();
    };
 
    MWindowsClientCredential.AllInstances.ClientCredentialSetNetworkCredential = (var1, var2) => { };
 
    MWindowsClientCredential.AllInstances.AllowNtlmGet = (var1) => { return true; };
 
    MWindowsClientCredential.AllInstances.AllowNtlmSetBoolean = (var1, var2) => { };
 
    MWindowsClientCredential.AllInstances.AllowedImpersonationLevelGet = (var1) => { return System.Security.Principal.TokenImpersonationLevel.Impersonation; };
 
    MWindowsClientCredential.AllInstances.AllowedImpersonationLevelSetTokenImpersonationLevel = (var1, var2) => { };
 
    MChannelFactory.AllInstances.CredentialsGet = (var1) => { return new MClientCredentials(); };
 
    MClientBase<TService>.AllInstances.ClientCredentialsGet = (var1) =>
    {
        return new System.ServiceModel.Description.ClientCredentials();
    };
 
    MClientBase<TService>.AllInstances.ChannelFactoryGet = (var1) =>
    {
        return new MChannelFactory01<TService>();
    };
 
    MClientBase<TService>.AllInstances.StateGet = (var1) =>
    {
        return PexChoose.EnumValue<CommunicationState>("CommunicationState");
    };
 
    MClientBase<TService>.AllInstances.Close = (var1) =>
    { };
 
    MClientBase<TService>.AllInstances.Abort = (var1) =>
    { };              
}

Run Pex Explorations to generate unit tests for GetResults method as displayed below
Unit tests will be added to the Test Class as displayed below
Go to Test View and Run the unit tests as displayed below

Summary:

In a similar way we can extract out external dependencies using Moles and then run Pex Explorations. A few examples of external dependencies can be data layer, UI layer, server calls etc. You can read more about Pex and Moles at http://research.microsoft.com/en-us/projects/pex/.

August 22, 2010January 18, 2019

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

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

Tools that are useful for creation of test data as well as ensuring that database schemas are synchronized
- Data generation
- Schema comparison
- Data comparison
More about unit testing a database which include
- Data driven unit tests
- Pre, test and post scripts
- Test conditions
- Transactions

Data Generation Plan:

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

Data Generation Plan: This template creates an empty data generation plan
Data Transform Plan: This template creates an data generation plan reusing data from another data source

Data generation plan Template:

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.

Data transform plan Template:

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:

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

Data Comparison:

The data from two databases can be compared as display below

This concludes the first logical part.

Data driven unit tests

In order to write a data driven unit test two important characteristics that we use are

DataSourceAttribute: This attribute provides data source specific information for data driven testing and is available in assembly Microsoft.VisualStudio.QualityTools.UnitTestFramework
TestContext: This class is used to store information that is provided to unit tests and is available in assembly Microsoft.VisualStudio.QualityTools.UnitTestFramework

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()

Common, Pre, Test and Post scripts:

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.

Test Conditions:

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

Transactions:

There are three ways through which we can specify transactions as listed below

Inside Transact-SQL
ADO.NET Transactions: Transactions can be implemented by adding code snippet to the test method as displayed 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

System.Transactions: Transactions can be implemented by adding code snippet to the test method as displayed below

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.

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

July 27, 2010January 18, 2019

How to unit test SQL Server 2008 database using Visual Studio 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 – 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

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: http://msdn.microsoft.com/en-us/library/aa833283.aspx

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