CODEWITHSUNDEEP
c#nunitintegration-testingxunitsql-server-data-tools
cs0815
cs0815

Reputation: 17418

SSDT projects + integration/end-to-end tests + TearDown

Let us say I have a sql server data tools project (SSDT) and a bunch of integration/end-to-end tests (deliberate not saying unit tests). Is there a way to TearDown and republish the existing test database via code from the test class library?

PS:

I am currently trying this (paths and file names simplified):

var p = new System.Diagnostics.Process
{
    StartInfo =
    {
    FileName =
        @"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /Action:Publish /SourceFile:“D:\Database_20151114_18-23-29.dacpac” Profile:”D:\LocalDatabase.publish.xml”",
    UseShellExecute = true
    }
};
p.Start();

Unfortunately, I am getting:

{"The system cannot find the file specified"}

The dacpac and xml file is definitely there. Any ideas? Also, is it possible to create the latest dacpac file automatically?

Upvotes: 1

Views: 593

Answers (3)

tomRedox
tomRedox

Reputation: 30543

Yes it's absolutely possible to automate the whole process. It roughly goes like this:

  • Add a reference to the Microsoft.Build library to your end-to-end tests project
  • Build your test project from code using that library to get an up to date DacPac
  • Add a reference to the Microsoft.SqlServer.Dac library using either NuGet or by installing the SSDT tools
  • Use the Dac related classes and methods in that library to publish a fresh copy of the database at the start of each test using the DacPac the build created.

This excellent existing answer has all the details on how to do that.

Upvotes: 1

cs0815
cs0815

Reputation: 17418

Something like this seems to do the trick:

const string dacPacFileName = @"D:\Bla.dacpac";
var connectionString = ConfigurationManager.ConnectionStrings["BlaConnectionString"].ConnectionString;

var dacPackage = DacPackage.Load(dacPacFileName);
var dacServices = new DacServices(connectionString);      
var dacOptions = new DacDeployOptions();
dacOptions.CreateNewDatabase = true;
dacServices.Deploy(dacPackage, "Bla", true, dacOptions);

However, it still does require the manual creation of a dapac file. Can this be automated as well?

Upvotes: 1

Keith
Keith

Reputation: 21264

Assuming SSDT is installed on the machine running the tests, you can run sqlpackage.exe via a shell command in TearDown. sqlpackage.exe is what SSDT uses to publish a database.

[TearDown]
public void TearDown() {
   System.Diagnostics.Process p = new System.Diagnostics.Process();
   p.StartInfo.FileName = @"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /Action:Publish /SourceFile:C:\DbProject\bin\Debug\DbProject.dacpac /TargetServerName:localhost /TargetDatabaseName:TestDb";
   p.StartInfo.UseShellExecute = true;
   p.Start();
}

See here for the sqlpackage.exe arguments. Note that sqlpackage.exe may be in a different path than the one I used above.

Upvotes: 1

Related Questions