Programmatically deploy database project in Visual Studio (for testing)

Question

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.

The best way is to start by deattaching the database from the server and then make copies of the database (a mdf file). Than you can attach the copies to the server so you have exact replicates of the database. You can attach the copies to a server under new names so you can have more than one copy of the database on a server. Deattach and Reattach can be done programmable in c#. Connecting to different server is just changing the connection string. The mdf files must be located physically on the machine where SQL Server is located. So the copies must be move to remote machines. — jdweng
– jdweng, Commented Feb 12, 2023 at 14:29

JayV · Accepted Answer · 2023-02-12 14:28:16Z

1

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);
}

answered Feb 12, 2023 at 14:28

JayV

3,2712 gold badges11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Techlead Over a year ago

Perfect - Thanks! - A side note for other readers, it seems to be the package Microsoft.SqlServer.DacFx that is required. There is a none Microsoft package under the namespace name.

Collectives™ on Stack Overflow

Programmatically deploy database project in Visual Studio (for testing)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related