Freigeben über

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

  • Artikel

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

image

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

image

Data generation plan Template:

Select the template i.e. Data Generation Plan and specify name and Add as displayed below

image

After you click Add you can customize various aspects of data generation plan in the Data generation plan designer as displayed below

image

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

image 

Once you are done configuring this plan go to Data –> Data Generator –> Generate Data as displayed below

image

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

image

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

image

The result when source and target schema are same is displayed below

image

The result when source and target schema were somewhat different is displayed below

image

Data Comparison:

The data from two databases can be compared as display below

image

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

image

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

image

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

Comments

  • Anonymous
    November 27, 2010
    Nice info, but you seem to have rushed through a very quick description of a lot of features while barely scratching the surface, rather than going in depth to provide usage examples Any chance you could expand on exactly how you would use Data Driven tests and how the TestContext works?  Does it allow you to run lots of tests for different parameter values, 1 per row of data fetched to drive the tests? Also some examples of how/what/why you would use common, pre, test and post scripts would be good Is it possible when using the Data checksum condition to automatically show the exact data differences on failure? Thanks

  • Anonymous
    March 16, 2012
    it seems its copied from : www.codeproject.com/.../How-to-unit-test-SQL-Server-2008-database-using-Vi

  • Anonymous
    March 16, 2012
    Sorry man !!!! its same you :)

  • Anonymous
    March 16, 2012
    The comment has been removed

  • Anonymous
    April 27, 2012
    Hi Interesting article, I'm going to read it in detail later. One question: Is VS 2010 Ultimate a pre-requisite for this or can it be done using VS 2010 Premium?

  • Anonymous
    April 30, 2012
    @peter ... 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. I presented a session on SSDT and you can find the content @ blogs.msdn.com/.../visual-studio-11-database-projects-and-unit-testing-sql-server-database.aspx 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."