SQlPackage For DacPac Deploy using .net C# System.Diagnostics.process

Question

I'm trying to call SQlPackage from a WinForms project using System.Diagnostics.process

Below is the code snippet I'm using to call Sqlpacakage with DACPAC path and connection string as argument

    ProcessStartInfo procStartInfo = new ProcessStartInfo();
    procStartInfo.FileName = @"C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\sqlpackage.exe";
    procStartInfo.Arguments = @"/Action:Publish /SourceFile:""C:\Myfiles\Samples\TestDatabse\Snapshots\TestDatabse_20191201_12 - 59 - 10.dacpac"" /TargetConnectionString:""Data Source = Server; Integrated Security = False; Initial Catalog = AdventureWorksDW2014; User ID = User; Password = Password; "" /p:DropObjectsNotInSource=False /p:BlockOnPossibleDataLoss=True /dsp:""C:\sqlOutput\Deploy.sql""";;
    procStartInfo.RedirectStandardOutput = true;
    procStartInfo.UseShellExecute = false;
    procStartInfo.CreateNoWindow = true;

    using (Process process = new Process())
    {
        process.StartInfo = procStartInfo;
        process.Start();

        process.WaitForExit();

        StreamReader reader = process.StandardOutput;
        string output = reader.ReadToEnd();
      }
        
        

When I execute the snippet the process is not waiting till the deployment of database. If I read the output I'm getting the first line of the output like Publishing to database 'AdventureWorksDW2014' on server ''. The process is getting exited after this and the database is not getting updated to given DACPAC.

Is there any suggested way where I can wait till the deployment of given DACPAC is done and DACPAC gets published in server.

Answer 1

From what it looks like, the exe is failing to process the install/publish. I might be wrong but it would be helpful to output the errors if there is any by redirecting the error output as well. Once you have your error, you should be able to troubleshoot from there.

    procStartInfo.RedirectStandardOutput = true;
    procStartInfo.UseShellExecute = false;
    procStartInfo.CreateNoWindow = true;
    procStartInfo.RedirectStandardError = true; // <--- Add this line

    using (Process process = new Process())
    {
        process.StartInfo = procStartInfo;
        process.Start();
        process.WaitForExit();


        // ---> I would add this here...
        var result = process.StandardOutput.ReadToEnd();
        string err = process.StandardError.ReadToEnd(); // <-- Capture errors

        if (!string.IsNullOrEmpty(err))
        {
           Console.WriteLine(err); // <---- Print any errors for troubleshooting
        }
        Console.WriteLine(result);
        // ----------------        
      }