Can you use the Visual Studio database project in the Unit Test project to install an empty database for a functional test?

Question

For many years, we used the following code to set up databases in the base class for our functional tests for our DAL, and it worked very 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 the Entity Framework does not install all the database components, and we would like to catch the inconsistencies between our EF DAL model and the actual database.

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

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

a) How to specify which database project to use for deployment?

b) How can I specify the connection string in the code and not app.config, for example, using localdb instead of 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 application in the SQL Unit Test project does not contain references to the original database project used to create it, and decompiles part of the test code and sees how it works, I do not see any indication. Does the solution assume there is only one database project?

Answer 1

In some direction from the @Ed Elliott links, I was able to do this. You need to add Microsoft.SqlServer.Dac as a reference to the assembly from C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.dll ( Visual Studio 2015). This is part of the SSDT toolkit, 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 will it be used for the functional test in the 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 pay attention to others, you should also set up your Build Dependencies so that your unit test project depends on the database project, ensuring that it is built first and creates the correct dacpac file.

What this solves for us is that it gives us a real database, and not just based on the Entity Framework model, which lacks quite a few SQL constructs (what to expect), especially the default constraints, indexes and other important database items. On our DAL layer, this is important to us.

Answer 2

I think the process that you have is a bit more complicated (if I understand it correctly, whatever I have!).

What I do for unit testing in ssdt:

• Create a solution
• Expand each dacpac I need for my dev instance
• Run tests

There are several ways to deploy a project:

• Create a publish profile for each project and run it
• Right click on the project and select publication
• Use the powershell script file (or do it in code in the initialization of the test) to publish dacpac.

After deployment, run your tests, publishing dacpac (project) is pretty simple from code or script, you can:

• calling sqlpackage.exe to do it for you
• use dacfx api for deployment ( http://blogs.msmvps.com/deborahk/deploying-a-dacpac-with-dacfx-api/ )

If you control the publication yourself, then it gives you more control plus, when you use this command, you test the same deployment system that you will use in other environments (assuming that you use dacpac for deployment).

e ed