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

Answer 1

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

Answer 2

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

Answer 3

Just checking....as I don't see the ability to generate test data in VS2012.  Is it truly available?

thanks

 - will

---

- will

Answer 4

Database Unit Testing != Data Generation Data Generation has not been delivered .

---

-GertD @ www.sqlproj.com

Answer 5

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