Here I've tried to create database out of the .bacpac with the help of .NET Core Web API using a PowerShell script. All I want to do is to pass dynamic parameters to the script from the API to create a database.
#=================================================================================
Designed to deploy a database from a dacpac
# #
Usage:
# # .\PublishDB.ps1 -targetServer "(LocalDB)\MSSQLLocalDB" -targetDB "Dummy3" -sourceFile "D:\DummyDatabase\bin\Debug\DummyDatabase.dacpac"
So, why would you do this when you could just call the sqlpackage.exe directly?
Because Powershell provides a higher level of orchestration; I plan to call this script from another script that first calls a script to build the .dacpac that is then used in this script.
Here is the Powershell script:
[CmdletBinding()]
Param(
#SQLPackage
# This directory for sqlpackage is specific to SQL Server 2012 (v11).
[Parameter(Mandatory=$false)]
[string]$sqlPackageFileName = "C:\Users\.dotnet\tools\sqlpackage.exe",
#Database connection
[Parameter(Mandatory=$false)]
[string]$targetServerName = "(LocalDB)\MSSQLLocalDB",
[Parameter(Mandatory=$false)]
[string]$targetDBname = "Dummy2",
#DacPac source
#Note PSScriptRoot is the location where this script is called from. Good idea to keep it in the root of
# your solution then the absolute path is easy to reconstruct
[Parameter(Mandatory=$false)]
[string]$sourceFile = "D:\DummyDatabase\bin\Debug\DummyDatabase.dacpac" #Quotes in case your path has spaces
)
& "$sqlPackageFileName" `
/Action:Publish `
/SourceFile:$sourceFile `
/TargetServerName:$targetServerName `
/TargetDatabaseName:$targetDBname `
And this is the C# code:
string script = @"D:\ItzMeIn_Database\Parki.ItzMeIn.Database\Parki.ItzMeIn.DB.WebAPI\PublishDB.ps1";
PowerShell powerShell = PowerShell.Create();
string script = @"D:\ItzMeIn_Database\Parki.ItzMeIn.Database\Parki.ItzMeIn.DB.WebAPI\PublishDB.ps1";
powerShell.AddCommand("Invoke-Command").AddScript(script).AddParameter("targetServerName", "targetServer").AddParameter("targetDBname", "Dummy3").AddParameter("sourceFile", @"D:\DummyDatabase\bin\Debug\DummyDatabase.dacpac");
powerShell.Invoke();
.AddCommand("Invoke-Command")AddCommand("Invoke-Command"- just invoke the script directly, which, however, also requires.AddCommand(), not.AddScript(). See the linked duplicate for details. Note that you may have to set the execution policy explicitly for your process, unless you know the system to be configured to allow script execution.