We have a micro service solution that includes:

• A SQL Server database project
• A Service project
• A Test project

We are interested to know if there is a programmatic way, ideally in C# to deploy the database project to a specified target server to enable us to seed and then run a set of API -> database tests. This is to aid both developer and CI/CD testing.

We have explored in memory and SQLite substitution options but ideally want to run tests as representative of production as possible to avoid the potential for the "quirks" of those solutions resulting in problems being missed.

>Solution :

Add a reference to: Microsoft.SqlServer.Dac.

For my usage, I create the UnitTesting database separately as an empty database, then run the Database project against this database.

DacServices instance = new DacServices(connectionString);
String path = Path.GetFullPath(@"..\..\..\..\DatabaseProjectFolder\bin\Debug\DatabaseProject.dacpac");

String databaseName = $"UnitTesting";

using (DacPackage dacPackage = DacPackage.Load(path))
{
    instance.Deploy(dacPackage, databaseName);
}