Execute SQL on PostgreSQL

executesql.ps1

Param
(
       
        [string]$ServerHostName, 
        [int]$ServerPort = 5432,
        [string]$User,
        [string]$Password,
        [string]$DBName,
        [string]$SQLFilePath = "",
		[string]$SQLCmd = ""
)

# Load ODBC Driver
[Reflection.Assembly]::LoadFrom('C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_3.2.1.0__5d8b90d52f46fda7\Npgsql.dll ') > $null

# Function to execute command
function ExecuteSQL ($SQLCmd,$ConnectionString)
{
    $DBConn = New-Object Npgsql.NpgsqlConnection
    $DBConn.ConnectionString = $ConnectionString;
    $DBConn.Open();
    $DBCmd = $DBConn.CreateCommand();
    $DBCmd.CommandText = $SQLCmd
    $DBCmd.ExecuteReader();
    $DBConn.Close();
}

# set connection string
$ConnectionString = "Host=$ServerHostName;Database=$DBName;Username=$User;Password=$Password;Command Timeout=300;Keepalive=30"
 
# if a command sent
if($SQLCmd -ne ""){

	ExecuteSQL -SQLCmd  $SQLCmd  -ConnectionString $ConnectionString

# if a file path
}else{
       
	ExecuteSQL -SQLCmd  (Get-Content  ($SQLFilePath)) -ConnectionString $ConnectionString 

}