Search code examples
c#entity-frameworkvisual-studiounit-testingsql-server-data-tools

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


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?


Solution

  • 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.