1

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();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
PrithiKumar
  • 11
  • 1
  • 1
    So what happens with the code you have now? What doesn't work? – ShamPooSham Feb 07 '23 at 09:48
  • When running in VS you do not have admin unless you start VS by right click shortcut and select Run As Admin. Powershell just runs sqlcmd.exe and you can call sqlcmd.exe from a process in c#. Using the powershell library is easier to use and using a process with slqcmd.exe. A PS1 should run exactly the same inside c# as outside provided the environmental variable (like PSModulePath) are the same and the credentials are the same. – jdweng Feb 07 '23 at 10:15
  • Remove `.AddCommand("Invoke-Command")` – Mathias R. Jessen Feb 07 '23 at 12:41
  • As Mathis states, there's no need for `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. – mklement0 Feb 07 '23 at 13:05

0 Answers0