ps/Modules/Alkami.PowerShell.Database/Public/Invoke-ExecuteQueryByConnectionString.ps1

function Invoke-ExecuteQueryByConnectionString {
<#
    .SYNOPSIS
    Executes a T-SQL script using a given connection string, using the ExecuteReader method

    .DESCRIPTION
    Executes a T-SQL script using a given connection string, using the ExecuteReader method
    This method will return one or more result sets according to the complexity of your query. Requesting multiple results will return multiple values.

    .PARAMETER ConnectionString
    The database connection string.

    .PARAMETER QueryString
    The query to execute

    .PARAMETER SqlInputParameters
    An optional hashtable array of input parameters which may be passed to the script.  Hashtable must be supplied with properties Name and Value

    .PARAMETER CommandTimeout
    The query timeout in seconds

    .PARAMETER WhatIf
    Run the query in a wrapped rollback transaction

    .PARAMETER Force
    Allow potentially destructive queries to be run. Please do not use this except with approval from leadership.

    .EXAMPLE
    $params = @( { Name = "@IsStolen"; Value = "1"; })
    > Invoke-ExecuteQueryByConnectionString -ConnectionString "data source=foo.local;Integrated Security=SSPI;Database=SuperMan2" -Query "SELECT Id, AccountId, Pennies, DateCreated FROM dbo.StolenPennies WHERE IsStolen = @IsStolen" -SqlInputParameters $params
    < [ { Id = 1; AccountId = 12345; Pennies = 12; DateCreated = '1969-12-31 12:59:59'; } ]
    #>

    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidAssignmentToAutomaticVariable', '', Justification = 'Event Handler Requirement')]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSReviewUnusedParameter', '', Justification = 'Sender is a Required Event Handler Parameter')]
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [string]$ConnectionString,

        [Parameter(Mandatory = $true)]
        [Alias('Sql')]
        [Alias('Query')]
        [string]$QueryString,

        [Parameter(Mandatory = $false)]
        [hashtable[]]$SqlInputParameters = @(),

        [Parameter(Mandatory = $false)]
        [int]$CommandTimeout = 30,

        [Parameter()]
        [switch]$WhatIf,

        [Parameter()]
        [switch]$Force
    )

    $logLead = Get-LogLeadName

    if (-not $Force) {
        if (-not (Assert-SqlQueryIsSafe -Query $QueryString)) {
            Write-Warning "$logLead : Script was not run. Please resolve errors and re-run"
            return
        }
    }

    if ($WhatIf) {
        $QueryString = ConvertTo-WhatIfQuery -Query $QueryString
    }

    $conn = New-Object System.Data.SqlClient.SqlConnection

    try {

        $conStrBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($ConnectionString)

    } catch [System.Management.Automation.MethodException] {

        Write-Warning "$logLead : Provided connection string [$ConnectionString] is invalid.  Execution cannot continue."
        return $null
    }

    if (-not (Confirm-DatabaseAccess -ConnectionString $conStrBuilder.ToString())) {

        Write-Warning "$logLead : Unable to connect to the database specified in connection string [$ConnectionString].  Execution cannot continue."
        return
    }

    $conn.ConnectionString = $conStrBuilder.ToString()
    Write-Verbose ("$logLead : Connecting to database with connection string {0}" -f $conStrBuilder.ToString())

    $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {

        param($sender, $eventArgs)

        # Handle if it's an error or informational message
        $hasErrors = ($null-ne $eventArgs.Errors -and $eventArgs.Count -gt 0)
        $consoleColor = ([System.ConsoleColor]::Gray)

        if ($hasErrors) {

            # We only want to write this as an "error" if the severity is greater than 10.  This aligns with SQL/SSMS
            $maxErrorLevel = ($eventArgs.Errors | Measure-Object -Property Class -Maximum).Maximum
            if ($maxErrorLevel -gt 10) {

                $consoleColor = ([System.ConsoleColor]::Red)
            }
        }

        Write-Host $eventArgs.Message -ForegroundColor $consoleColor
    };

    try {

        $conn.add_InfoMessage($handler);
        $conn.FireInfoMessageEventOnUserErrors = $true

        $conn.Open()
        $query = New-Object System.Data.SqlClient.SqlCommand($QueryString, $conn)
        $query.CommandTimeout = $CommandTimeout

        if (-NOT (Test-IsCollectionNullOrEmpty $SqlInputParameters)) {

            foreach ($parameter in $SqlInputParameters) {

                Write-Verbose "$logLead : Adding parameter [$($parameter.Name)] with value [$($parameter.Value)] to the SqlCommand"
                $query.Parameters.AddWithValue($parameter.Name, $parameter.Value) | Out-Null
            }
        }

        $reader = $query.ExecuteReader()

        $allResults = New-Object -TypeName "System.Collections.ArrayList"
        do {
            $currentResults = @()
            $columns = @()

            # Collect all the column names once per result set so we can use those as property names
            for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                try {
                    $columns += $reader.GetName($i)
                } catch {
                    # Use a default value in the case of an error reading this column name (ex: unnamed columns being returned from the server)
                    $columns += "Column$i"
                }
            }

            # For each result in the result set ... bog standard ADO.NET code
            while ($reader.Read()) {
                $rowResult = @{}
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    # This chunk of code will assign a variable to an object with the column name
                    # example: `select count(*) from table` returns an unnamed column, which gets a default name from SQL. If the column doesn't have a name, we default to Column1, Column2, etc (assigned above)
                    # example: `select count(*) as count from table` will return a column named count
                    # We would then name the result as $rowResult.count = $null or $rowResult.count = $reader[$i]
                    $columnName = $columns[$i]

                    # IsDBNull is a special value that causes errors if not handled correctly
                    if ($reader.IsDBNull($i)) {
                        $rowResult[$columnName] = $null
                    } else {
                        # This will automatically cast to the correct type, but PS is also type-forgiving, so we just take the naive value
                        $rowResult[$columnName] = $reader[$i]
                    }
                }
                $currentResults += $rowResult
            }
            $allResults.Add($currentResults) | Out-Null
        } while ($reader.NextResult())

        return $allResults

    } catch {

        Write-Warning "$logLead : An exception occurred while trying to execute the specified query against the database"
        Write-Warning "$logLead : $($_.ToString())"
        Write-Warning "$logLead : $($_.ScriptStackTrace)"
        return $null

    } finally {

        if (($null -ne $conn) -and ($conn.State -ne [System.Data.ConnectionState]::Closed)) {

            $conn.Close()
        }

        $conn = $null
    }
}