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

  • Article

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

  • Microsoft.VisualStudio.QualityTools.UnitTestFramework: This assembly is the testing framework assembly the test engine depends on when executing the unit tests. I won’t get into details of the parts of this assembly and focus on some the the important attributes that are required to execute an database unit test.

  • Microsoft.Data.Schema.UnitTesting: This assembly provides the classes required to create and execute database unit test. In this blog I’ll focus on this assembly.

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.

Database Unit Test Designer:

I have highlighted the various options available in Database Unit Test Designer and added short description as displayed below

image

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

Microsoft.VisualStudio.QualityTools.UnitTestFramework:

This assembly is the testing framework assembly that the unit test engine depends upon while executing unit tests.

Microsoft.VisualStudio.QualityTools.UnitTestFramework:

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.

  • TestClassAttribute: The class containing test methods should be marked with this attribute else the tests won’t execute. This attribute can only be applied to a class.

  • AssemblyInitializeAttribute: Method marked with this attribute will be run prior to methods marked with ClassInitializeAttribute, TestInitializeAttribute, and TestMethodAttribute attributes. This attribute can only be applied to a method. Only one method in an assembly may be decorated with this attribute. An example of usage of this attribute is setting up database based upon setting from a configuration file i.e. deploying database, generating data.

  • AssemblyCleanupAttribute: Method marked with this attribute will be run after all the test methods  in that assembly will execute i.e. after methods marked with TestMethodAttribute, TestCleanupAttribute and ClassCleanupAttribute attributes. This attribute can only be applied to a method. Only one method in an assembly may be decorated with this attribute. This method won’t execute  if there is any exception in the method marked with AssemblyInitialize attribute.

  • ClassInitializeAttribute: Method marked with this attribute will run only once in the entire test. This attribute can only be applied to a method. This method runs before the method marked with TestInitialize attribute and after the method marked with AssemblyInitialze attribute. A class can have only one method marked with this attribute.

  • ClassCleanupAttribute: Method marked with this attribute will run only once in the entire test. This attribute can only be applied to a method. This method runs before the method marked with AssemblyCleanup attribute and after the method marked with TestCleanup attribute. A class can have only one method marked with this attribute. This method won’t execute  if there is any exception in the method marked with ClassInitialize attribute.

  • TestMethodAttribute: Test method should be marked with this attribute else method won’t execute. This attribute can only be applied to a method.

  • TestInitializeAttribute: The method that is marked with this attribute runs before each test is executed. This method runs before methods marked with TestMethod attribute and after method marked with ClassInitialize attribute. This attribute can only be applied to a method. A class can have only one method marked with this attribute.

  • TestCleanupAttribute: The method that is marked with this attribute runs after each test is executed. This method runs before method marked with ClassCleanup attribute and after methods marked with TestMethod attribute. This attribute can only be applied to a method. A class can have only one method marked with this attribute. This method won’t execute  if there is any exception in the method marked with TestInitialize attribute.

  • DataSourceAttribute: This attribute provides data-source specific information for data-driven testing.

  • PriortyAttribute: This attribute is used to specify the priorty of an unit test.

  • IgnoreAttribute: This attribute is used to ignore the unit test i.e. specific test should not run.

  • ExpectedExceptionAttribute: This is used in case exception is expected during execution of unit test.

  • Assert: This class verifies conditions in unit test.

  • TestContext: This class is used to store information that is provided to unit tests.

  • TestConfigurationSection: This class provides access to data source configuration data.

  • TestConfiguration: This class provides access to a TestConfigurationSection that represents the microsoft.visualstudio.testtools section in an app.config file.

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

  1. Methods marked with AssemblyInitialize and AssemblyCleanUp attributes will execute only once for an assembly.

  2. Methods marked with ClassInitialize and ClassCleanUp attributes will execute only once for each class in an assembly.

  3. Methods marked with TestInitialize and TestCleanUp attributes will execute before and after respectively for each test method(i.e. method marked with TestMethod attribute).

image

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.

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

image

Microsoft.Data.Schema.UnitTesting:

This namespace provides classes that you can use to create and execute database unit tests. The classes are listed below

  • DatabaseTestClass: This is the base calss for a database unit test. This class derives from Component. For more information about choosing between IComponent, Component, IDisposable, MarshalByValueComponent and Control while implementing a class please read my blog How to Choose Component, IComponent ....

  • ConnectionContext: This class encapsulated the database connection associated with the database unit test. In the DatabaseTestClass we have two properties of this type i.e. ExecutionContext and PrivilegedContext. The ExecutionContext and PrivelegedContext differ only if SQL Server authentication is used. If Windows authentication is used same credentials will be used for both. There is a reason behind having two separate contexts inside a DatabaseTestClass

    • ExecutionContext: This defines the context used to execute the Transact-Sql script associated with an unit test. Ideally connection string should have same credentials as that of end user. This is the ExecutionContext element in the App.config.

    • PrivilegedContext: This is optional and defines the context used for running the pre-test action, post-test action, TestInitialize, and TestCleanup scripts. This connection string is also used to deploy database changes and generate data. This is the PrivilegedContext element in the App.config. If unit tests run the test script only, then there is no need to specify a privileged context.

  • DatabaseTestAction: This class contains Transact-Sql test script and test actions.

  • DatabaseTestActions: This class is the design time equivalent of test and contains PostTestAction, PreTestAction and TestAction that are of type DatabaseTestAction.

  • DatabaseTestService: This class provides methods to Deploy database project, Execute test, Generate data and opening of Execution context and privileged execution contexts.

  • ExecutionResult: This is the result for and unit test and contains Dataset, execution time and rows affected.

  • ExpectedSqlExceptionAttribute: This attribute specifies that unit test is expecting a SQL exception and optionally specify the error number, severity, and state of the expected error.

  • TestScript: This class represents a generated SQL test

  • UnitTestingDesignService: Provides the base class for database schema providers to generate scripts for objects in their project system.

Microsoft.Data.Schema.UnitTesting.Configuration:

This namespace provides classes used to read or write configuration information to the DatabaseUnitTesting section of the app.config. The classes are listed below

  • ConnectionContextElement: This class represents the ExecutionContext or PrivilegedContext configuration element in the DatabaseUnitTesting section of the app.config file

  • DatabaseDeploymentElement: This class represents the DatabaseDeployment configuration element in the DatabaseUnitTesting section of the app.config file.

  • DatabaseUnitTestingSection: This class represents the DatabaseUnitTesting configuration section in the app.config file.

  • DatabaseGenerationElement: This class represents the DataGeneration configuration element in the DatabaseUnitTesting section of the app.config file.

Microsoft.Data.Schema.UnitTesting.Conditions:

This namespace provides classes that you can use to create and manage test conditions. The classes are listed below

  • TestCondition: Base class of all test conditions.

  • ChecksumCondition: This condition verifies the checksum of the result set in a database unit test.

  • DataSetTestCondition: This is the base class for test conditions that operate on a DataSet and are configured by the user during test "design time".

  • EmptyResultSetCondition: For this test conditon to succeed Execution.DataSet property must be empty.

  • ExecutionTimeCondition: If test takes more time to execute than specified by this condition test fails.

  • ExpectedSchemaCondition: Specifies that result set of a database unit test must match a specific schema.

  • InconclusiveCondition: Represents a default condition where nothing is required or expected of a test result.

  • NotEmptyResultSetCondition: For this test conditon to succeed Execution.DataSet property should not be empty.

  • ResultSetCondition: Represents the base class for all test conditions that operate on one ResultSet.

  • RowCountCondition: Defines a test condition that verifies the number of rows in the ResultSet that is returned by a database unit test.

  • ScalarValueCondition: Verifies that a particular value in a specific row and column was expected.

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

del.icio.us Tags: Unit Test,SqL Server 2008,Visual Studio 2010