Functions/Invoke-SqlQuery.ps1

function Invoke-SqlQuery {
    <#
        .Synopsis
            Run SQL query and return resulting tables and/or output messages
        .DESCRIPTION
            Invoke a SQL command. For us not able to use the fully featured invoke-sql from the SQL server:
     
            https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps
        .PARAMETER Query
            The query to run.
        .PARAMETER Server
            Name of server to connect to (using std authentication)
        .PARAMETER Database
            Name of database to connect to (using std authentication)
        .PARAMETER ConnectionString
            Fully featured connection string
        .NOTES
            For a full feature SQL Admin module: https://dbatools.io/
        #>

        [CmdletBinding(DefaultParameterSetName='NamedConnection')]
        Param(
        [parameter(Position=0,mandatory=$true)]
        [string]$Query,       
        [parameter(ParameterSetName='NamedConnection')]
        [string]$Server,
        [parameter(ParameterSetName='NamedConnection')]
        [string]$DataBase,
        [parameter(ParameterSetName='ConnectionString')]
        [string]$ConnectionString
        )
        Begin
        {
            if ([string]::IsNullOrEmpty($ConnectionString)) {
                $ConnectionString="server='$Server';database='$Database';trusted_connection=true;"
            }
            #Establish connection
            $Connection = New-Object System.Data.SQLClient.SQLConnection
            $Connection.ConnectionString = $ConnectionString
            
            [string]$global:tmpInfoMessagesFromSQLcommand = ""
            $InfoHandler = [System.Data.SqlClient.SqlInfoMessageEventHandler]{param($sender, $event) $global:tmpInfoMessagesFromSQLcommand += "$($event.Message)`r`n"}
            $Connection.add_InfoMessage($InfoHandler);
            $Connection.FireInfoMessageEventOnUserErrors = $true;            
            
            $Connection.Open()
            $Command = New-Object System.Data.SQLClient.SQLCommand
            $Command.Connection = $Connection
    
            $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
            $SqlAdapter.SelectCommand = $Command
        }
        Process
        {
                   
            $Command.CommandText = $Query
            $DataSet = New-Object System.Data.DataSet
            $SqlAdapter.Fill($DataSet) | Out-Null
    
            #Return object with a separate buckets for data and messages.
            #The use of DataSet has some VERY nice features (ask Google if you don't beleive it)
            #as well as the possibility to return several data tables in one query
            $return = [PSCustomObject]@{
                Messages = $global:tmpInfoMessagesFromSQLcommand
                DataSet = $DataSet
            }
    
            $return
                    
        }
        End
        {
            #Empty tmp-variable
            [string]$global:tmpInfoMessagesFromSQLcommand = $null
    
            #Close connection
            $Connection.Close()
        }
    }