<#  
.SYNOPSIS  
    Powershell script to execute a sql command from powershell when the machine does not have sql server or sql management studio installed
  
.PARAMETER serverAddress
    Name of the SQL server: eg: SQL-SERVER\SqlExpress
.PARAMETER database
    Name of the database: eg: MyDatabase
.PARAMETER user
    User name for accessing SQL server: eg: sa
.PARAMETER pwd
    Password for accessing SQL server: eg: YourPassword
.PARAMETER fileWithSqlCommand
    Name and path of the file with the SQL Command: eg: c:\scratch\sqlCommand.sql      

.NOTES
   # https://stackoverflow.com/a/18126782/18169
#> 

param
(
	[parameter(Mandatory=$true)] 
	[string]$serverAddress,
	
	[PARAMETER(Mandatory=$true)]
	[string]$database,
	
	[PARAMETER(Mandatory=$true)]
	[string]$user,
	[PARAMETER(Mandatory=$true)]
	[string]$pwd,
	[PARAMETER(Mandatory=$true)]
	[string]$fileWithSqlCommand
)

function executeWithSqlConnection
{
	#When using windows autentication
	#"Server=$serverAddress;Database=$database;Trusted_Connection=True"
	$Conn=New-Object System.Data.SQLClient.SQLConnection "Server=$serverAddress;Database=$database;User Id=$user;password=$pwd";
	$Conn.Open();
	$DataCmd = New-Object System.Data.SqlClient.SqlCommand;
	$MyQuery = get-content $fileWithSqlCommand;
	$DataCmd.CommandText = $MyQuery;
	$DataCmd.Connection = $Conn;
	$DAadapter = New-Object System.Data.SqlClient.SqlDataAdapter;
	$DAadapter.SelectCommand = $DataCmd;
	$DTable = New-Object System.Data.DataTable;
	$DAadapter.Fill($DTable)|Out-Null;
	$Conn.Close();
	$Conn.Dispose();
	$DTable;
}

#Only works in Windows Powershell, not in PowerShell core
function executeWithSqlCmd
{
	invoke-sqlcmd -ServerInstance $serverAddress -Database $database -UserName $user -Password $pwd -InputFile $fileWithSqlCommand
}

executeWithSqlConnection
#executeWithSqlCmd