Can you use a Visual Studio Database Project in a Unit Test Project to setup a empty database for a functional test?

Question

For years we have used the following code to setup databases in a base class for our functional tests for our DAL, and this has worked extremely well for us.

/// <summary>
/// Initializes the test class by creating the integration database.
/// </summary>
[TestInitialize]
public virtual void TestInitialize()
{
    DataContext = new DataContext(ConnectionString);

    CleanupPreviousTestRunDatabases();

    if (DataContext.Database.Exists())
    {
        DataContext.Database.Delete();
    }

    DataContext.Database.Create();
    DataContext.Database.ExecuteSqlCommand(String.Format(Strings.CreateLoginCommand, DatabaseUserName, DatabasePassword));
    DataContext.Database.ExecuteSqlCommand(String.Format("CREATE USER {0} FOR LOGIN {0}", DatabaseUserName));
    DataContext.Database.ExecuteSqlCommand(String.Format("EXEC sp_addrolemember 'db_owner', '{0}'", DatabaseUserName));
}

However, using Entity Framework does not setup all components of a database and we would like to catch discrepancies between our EF DAL model and the actual database.

We use the SSDT tools / Visual Studio Database Project for all of our database work, and I know you can write SQL unit tests, and in those SQL unit tests, I have seen the ability to setup and create a database based on the database project itself. This is what I would like to do, but from our other functional test libraries.

I can reference the libraries and write some of the setup code, but what I'm looking for is:

a) How do I provide which Database project to use to deploy?

b) How can I specify connection string in code rather than an app.config, such as using localdb instead with a dynamically named database?

namespace Product.Data.Tests
{
    using Microsoft.Data.Tools.Schema.Sql.UnitTesting;
    using Microsoft.VisualStudio.TestTools.UnitTesting;

    [TestClass]
    public class FunctionalTest
    {
        [TestInitialize]
        public virtual void TestInitialize()
        {
            SqlDatabaseTestClass.TestService.DeployDatabaseProject();
            SqlDatabaseTestClass.TestService.GenerateData();
        }
    }
}

The app.config in a SQL Unit Test Project doesn't contain any reference back to the original Database project used to create it, and decompiling some of the test code and seeing how it works, I don't see any indication. Does it assume there is only one database project in the solution?

Answer 1

With some direction from the links @Ed Elliott posted, I was able to make this happen. You will need to add Microsoft.SqlServer.Dac as a assembly reference from C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.dll (Visual Studio 2015). It is part of the SSDT tooling, so I'm sure the path may be different for earlier versions.

[TestClass]
public class DatabaseTest
{
    protected string DatabaseConnectionString = $@"Data Source=(localdb)\v11.0; Integrated Security=True";
    protected DatabaseContext DatabaseContext;
    protected string DatabaseName = $"UnitTestDB_{Guid.NewGuid().ToString("N").ToUpper()}";

    public TestContext TestContext { get; set; }

    [TestInitialize]
    public virtual void TestInitialize()
    {
        var instance = new DacServices(DatabaseConnectionString);
        var path     = Path.GetFullPath(Path.Combine(TestContext.TestDir, 
                                        @"..\..\..\Build\Database\Database.dacpac"));

        using (var dacpac = DacPackage.Load(path))
        {
            instance.Deploy(dacpac, DatabaseName);
        }

        DatabaseContext = new DatabaseContext(DatabaseConnectionString);
    }

    [TestCleanup]
    public virtual void TestCleanup()
    {
        DeleteDatabase(DatabaseName);
    }
}

Then how it would be used for a functional test in a unit test project.

[TestClass]
public class CustomerTypeTests : DatabaseTest
{
    private CustomerType customerType;

    [TestInitialize]
    public override void TestInitialize()
    {
        base.TestInitialize();

        customerType = new CustomerType
                       {
                           Name = "Customer Type"
                       };
    }

    [TestMethod]
    public void AddOrUpdateCustomerType_ThrowExceptionIfNameIsNull()
    {
        ExceptionAssert.Throws<ArgumentNullException>(() => DatabaseContext.AddOrUpdateCustomerType(customerType));
    }
}

Just a note to others, you should also setup your Build Dependencies so that your unit test project depends on the database project, ensuring it is built first and produces the correct dacpac file.

What this solves for us, is this gives us a true database, not one just based on Entity Framework's model, which lacks quite a lot of SQL constructs (to be expected), especially default constraints, indexes, and other important elements of a database. At our DAL layer, this is essential for us.

Answer 2

I think the process you have is a little over complicated (if I understand it correctly which I might not have!).

What I do for unit testing in ssdt is to:

• Build the solution
• Deploy each dacpac that I need to my dev instance
• Run the tests

To deploy a project there are a few ways, you can:

• Create a "Publish Profile" for each project and run that
• Right click on the project and choose publish
• Use a powershell script (or do it in code in your test initialize) to do a publish of the dacpac.

Once it is deployed run your tests, doing a publish of a dacpac (project) is pretty simple from code or a script, you can either:

• call sqlpackage.exe to do it for you
• use the dacfx api's to do the deploy (http://blogs.msmvps.com/deborahk/deploying-a-dacpac-with-dacfx-api/)

If you control the publish yourself then it gives you a lot more control plus when you deploly using this you are testing the same deployment system that you will use in other environments (assuming you use dacpac's to deploy).

ed