Invoke a T-SQL batch file or string using ADO.NET and Power Shell

Invoke-AdoNetSqlCmdl.ps1

$ErrorActionPreference = "Stop"
Set-StrictMode -Version 3.0

<#-------------------------------------------------------------------------- 
https://gallery.technet.microsoft.com/scriptcenter/The-PowerShell-script-for-2a2456c4


.SYNOPSIS 
Script for  running T-SQL files in MS SQL Server 
Andy Mishechkin
 
.DESCRIPTION 
runsql.ps1 has a next command prompt format: 
Invoke-AdoNetSqlCmd -server MSSQLServerInstance -dbname ExecContextDB -file MyTSQL.sql [-go] [-u SQLUser] [-p SQLPassword] 
 
Mandatory parameters: 
-server - name of Microsoft SQL Server instance  
-dbname - database name for  T-SQL execution context (use the '-dbname master' for  creation of new database) 
 
Optional parameters: 
-sqlfile - name of .sql file, which contain T-SQL code for  execution 
-sql - T-sql to execute
-go - parameter-switch, which must be, if  T-SQL code is contains 'GO'  statements. If you will use the -go switch for T-SQL script, which is not contains 'GO'-statements - this  script will not execute 
-u - the user name if  using Microsoft SQL Server authentication 
-p - the password  if  using Microsoft SQL Server authentication 
 
Examples. 
 
1) Execute on local SQL Server the script CreateDB.sql, which is placed in  C:\MyTSQLScripts\ and contains 'GO'  statements, using 
 
Windows credentials of current user: 
Invoke-AdoNetSqlCmd -server local -dbname master -file C:\MyTSQLScripts\CreateDB.sql -go 
 
2) Execute on remote SQL Server Express with   
machine name 'SQLSrvr'  the script CreateDB.sql, which is placed in C:\MyTSQLScripts\ and  
contains 'GO' statements, using SQL Server user name 'sa' and password 'S@Passw0rd': 
Invoke-AdoNetSqlCmd -server SQLSrvr\SQLEXPRESS -dbname master -file C:\MyTSQLScripts\CreateDB.sql -go -u sa -p S@Passw0rd 
 
---------------------------------------------------------------------------#> 

Function Invoke-AdoNetSqlCmd
{    

	#Script parameters 
	param( 
					#Name of MS SQL Server instance 
					[parameter(Mandatory=$true, 
								 HelpMessage="Specify the SQL Server name where will be run a T-SQL code",Position=0)] 
					[String] 
					[ValidateNotNullOrEmpty()] 
					$server = $(throw "sqlserver parameter is required."), 
 
					#Database name for execution context 
					[parameter(Mandatory=$true, 
								 HelpMessage="Specify the context database name",Position=1)] 
					[String] 
					[ValidateNotNullOrEmpty()] 
					$dbname = $(throw "dbname parameter is required."), 
 
					#Name of T-SQL file (.sql) 
					[parameter(Mandatory=$false, 
								 HelpMessage="Specify the name of T-SQL file (*.sql) which will be run",Position=2)] 
					[String] 
					$sqlFile,
 
 					[parameter(Mandatory=$false, 
								 HelpMessage="Specify the name of T-SQL file (*.sql) which will be run",Position=3)] 
					[String] 
					[AllowEmptyString()] 
					$sql,

					#The GO switch. Must be specified if T-SQL code is contain the GO instructions 
					[parameter(Mandatory=$false,Position=4)] 
					[Switch] 
					[AllowEmptyString()] 
					$go, 
 
					#MS SQL Server user name 
					[parameter(Mandatory=$false,Position=5)] 
					[String] 
					[AllowEmptyString()] 
					$u, 
 
					#MS SQL Server password name 
					[parameter(Mandatory=$false,Position=6)] 
					[String] 
					[AllowEmptyString()] 
					$p 
			) 

	if ([string]::IsNullOrWhitespace($sqlFile))
	{
		if ($sql -eq $null)
		{
			throw "sql or file must be specified."
		}

		$file = [System.IO.Path]::GetTempFileName()
		$sql | Out-File -Encoding utf8 -FilePath $file
	}
    else
    {
        $file = $sqlFile
    }

	#Connect to MS SQL Server 
	try 
	{ 
			$SQLConnection = New-Object System.Data.SqlClient.SqlConnection 
			#The MS SQL Server user and password is specified 
			if($u -and $p) 
			{ 
					$SQLConnection.ConnectionString = "Server=" + $server + ";Database="  + $dbname + ";User ID= "  + $u + ";Password="  + $p + ";" 
			} 
			#The MS SQL Server user and password is not specified - using the Windows user credentials 
			else 
			{ 
					$SQLConnection.ConnectionString = "Server=" + $server + ";Database="  + $dbname + ";Integrated Security=True" 
			} 
			$SQLConnection.Open() 
	} 
	#Error of connection 
	catch 
	{ 
			throw $Error[0]
	} 
	#The GO switch is specified - parsing T-SQL code with GO 
	if($go) 
	{ 
			$SQLCommandText = @(Get-Content -Path $file) 
            $SQLCommandText += "GO"

			foreach($SQLString in  $SQLCommandText) 
			{ 
					if($SQLString -ne "GO") 
					{ 
							#Preparation of SQL packet 
							$SQLPacket += $SQLString + "`n" 
					} 
					else 
					{ 
							$IsSQLErr = $false 
							#Execution of SQL packet 
							try 
							{ 
									if ([string]::IsNullOrWhiteSpace($SQLPacket) -eq $false)
									{
										$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLPacket, $SQLConnection) 
										$SQLCommand.ExecuteScalar() 
									}
							} 
							catch 
							{ 
									$SQLPacket | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append 
									$Error[0] | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append 
									"----------" | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append 

									throw $Error[0]
							} 

							$SQLPacket = "" 
					} 
			} 
	} 
	else 
	{ 
			#Reading the T-SQL file as a whole packet 
			$SQLCommandText = @([IO.File]::ReadAllText($file)) 
			#Execution of SQL packet 
			try 
			{ 
					$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLCommandText, $SQLConnection) 
					$SQLCommand.ExecuteScalar() 
			} 
			catch 
			{ 
					throw $Error[0]
			} 
	} 
	#Disconnection from MS SQL Server 
	$SQLConnection.Close() 
}