none
Database Unit Test support in SSDTs

    Question

  • Hello,

    I'm using the SSDTs to develop a database project. Now I want to test the project with unit tests. I created a unit test project with a database unit test. 

    First, is this the right way to test an SSDT database project?

    Additionally...

    In the Test database configuration I wanted to select a database-project, which should be deployed before the tests will be executed. Therefore I have to choose an *.dbproj file. But my database project is an *.sqlproj file. What is the difference.

    I'm very new to database unit testing. Maybe these are pretty stupid questions.

    Anyway, I appreciate every help!

    Cincerely,

    Uli

    Thursday, June 14, 2012 3:16 PM

Answers

  • SSDT does not come with database unit testing, like Visual Studio 2010 database projects (*.dbproj). However you can use the Visual Studio 2010 Databse Unit Tests in combination with SQL Server Data Tools database projects (*.sqlproj). You will not have Schema View integration (Create Unit Test), data generation does not work and the test framework will not automaticly deploy the database.

    To make it work you simply update the database connection string in the app.config, to point it to the (localdb) instance of your project, in my case this was (localdb)\DBProjUnitTest and set the Initial Catalog to the database name.

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <configSections>
            <section name="DatabaseUnitTesting" type="Microsoft.Data.Schema.UnitTesting.Configuration.DatabaseUnitTestingSection, Microsoft.Data.Schema.UnitTesting, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
        </configSections>
        <DatabaseUnitTesting>
            <DataGeneration ClearDatabase="true" />
            <ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=(localdb)\DBProjUnitTest;Initial Catalog=DBProjDB;Integrated Security=True;Pooling=False"
                CommandTimeout="30" />
            <PrivilegedContext Provider="System.Data.SqlClient" ConnectionString="Data Source=(localdb)\DBProjUnitTest;Initial Catalog=DBProjDB;Integrated Security=True;Pooling=False"
                CommandTimeout="30" />
        </DatabaseUnitTesting>
    </configuration>

    In this state you have to deploy manually (hit F5) and run test seperately.

    You can override the code in DatabaseSetup.cs to deploy the database.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Configuration;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using Microsoft.Data.Schema.UnitTesting;
    namespace DBProjTest
    {
        [TestClass()]
        public class DatabaseSetup
        {
            [AssemblyInitialize()]
            public static void InitializeAssembly(TestContext ctx)
            {
                //   Setup the test database based on setting in the
                // configuration file
                DatabaseTestClass.TestService.DeployDatabaseProject();
                DatabaseTestClass.TestService.GenerateData();
            }
        }
    }
    You can override the public virtual void DeployDatabaseProject(); in the database test framework, by calling the DACFX deployment API.


    -GertD @ www.sqlproj.com

    Saturday, June 16, 2012 11:02 PM

All replies

  • SSDT does not come with database unit testing, like Visual Studio 2010 database projects (*.dbproj). However you can use the Visual Studio 2010 Databse Unit Tests in combination with SQL Server Data Tools database projects (*.sqlproj). You will not have Schema View integration (Create Unit Test), data generation does not work and the test framework will not automaticly deploy the database.

    To make it work you simply update the database connection string in the app.config, to point it to the (localdb) instance of your project, in my case this was (localdb)\DBProjUnitTest and set the Initial Catalog to the database name.

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <configSections>
            <section name="DatabaseUnitTesting" type="Microsoft.Data.Schema.UnitTesting.Configuration.DatabaseUnitTestingSection, Microsoft.Data.Schema.UnitTesting, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
        </configSections>
        <DatabaseUnitTesting>
            <DataGeneration ClearDatabase="true" />
            <ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=(localdb)\DBProjUnitTest;Initial Catalog=DBProjDB;Integrated Security=True;Pooling=False"
                CommandTimeout="30" />
            <PrivilegedContext Provider="System.Data.SqlClient" ConnectionString="Data Source=(localdb)\DBProjUnitTest;Initial Catalog=DBProjDB;Integrated Security=True;Pooling=False"
                CommandTimeout="30" />
        </DatabaseUnitTesting>
    </configuration>

    In this state you have to deploy manually (hit F5) and run test seperately.

    You can override the code in DatabaseSetup.cs to deploy the database.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Configuration;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using Microsoft.Data.Schema.UnitTesting;
    namespace DBProjTest
    {
        [TestClass()]
        public class DatabaseSetup
        {
            [AssemblyInitialize()]
            public static void InitializeAssembly(TestContext ctx)
            {
                //   Setup the test database based on setting in the
                // configuration file
                DatabaseTestClass.TestService.DeployDatabaseProject();
                DatabaseTestClass.TestService.GenerateData();
            }
        }
    }
    You can override the public virtual void DeployDatabaseProject(); in the database test framework, by calling the DACFX deployment API.


    -GertD @ www.sqlproj.com

    Saturday, June 16, 2012 11:02 PM
  • Update: SSDT has added support for database unit testing in the December 2012 release

    http://blogs.msdn.com/b/ssdt/archive/2012/12/13/available-today-ssdt-december-2012.aspx

    • Proposed as answer by TerryChuangMVP Friday, December 14, 2012 6:46 AM
    Thursday, December 13, 2012 5:39 PM
  • Just checking....as I don't see the ability to generate test data in VS2012.  Is it truly available?

    thanks

     - will


    - will

    Thursday, February 14, 2013 8:47 PM
  • Database Unit Testing != Data Generation Data Generation has not been delivered .

    -GertD @ www.sqlproj.com

    Thursday, February 14, 2013 8:55 PM
  • Thanks for the response.  Sorry to hear that Data Generation hasn't been delivered/included.  And, also sorry I moved to VS2012 as I used that feature a lot.  Looks like it is time for me to uninstall and return to the previous version until msft decides to add the ability to generate test data again.

    - will


    - will

    Thursday, February 14, 2013 10:36 PM
  • I don't know why Microsoft doing all this. We are trusting microsoft framework and implement some thing on earlier versions suddenly if they disapper in new versions ... haaah. Atleast till you find the new feature please keep the existing one.

    We have implemented recently DB unit test project using VS 2010 and almost we are about to get the benfit out of that and we spend hell lot of time to write the unit test projects. Suddenly after converting VS 2010 to VS 2012. Nothing going to work and to make it work we need to do lot of changes and now we need to find the way how we can create the DB unit tests for the SP's etc....

    Thursday, March 27, 2014 5:31 PM