Executes and returns the results of an SQL query in PowerShell.

Invoke-Sql.ps1

function Invoke-Sql {
    <#
        .SYNOPSIS
        Executes and returns the results of an SQL query.

        .EXAMPLE        
        $results = Invoke-Sql -ConnectionString "Server=TEST-SERVER\SQLEXPRESS;Database=Test_DB;Integrated Security=True" -Query "SELECT * FROM Employee"
        
        Calls an SQL query and saves the results.
        
        .EXAMPLE
        @(10, 13, 23) | ForEach-Object { return "UPDATE Employee SET empSalary *= 1.2 WHERE empPrimaryKey = $_" } | Invoke-Sql -ConnectionString "Server=TEST-SERVER\SQLEXPRESS;Database=Test_DB;Integrated Security=True"
        
        Uses the pipeline to execute multiple queries without closing the connection.
    #>
    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true)]
        [System.String]
        $ConnectionString,
        
        [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
        [System.String]
        $Query,

        [Parameter(Mandatory=$false)]
        [System.Action[string]]
        $LogAction = {param($m) Write-Debug $m},

        [Parameter(Mandatory=$false)]
        [Switch] 
        $WhatIf
    )
    begin {
        $sqlConn = [System.Data.SqlClient.SQLConnection]::new()
        $sqlConn.ConnectionString = $ConnectionString
        $LogAction.Invoke("Openning connection: $ConnectionString")
        $sqlConn.Open()        
    }
    process {
        if ($WhatIf) {
            Write-Host "Would execute '$Query'"
            return ,@()
        }
        else {
            try {
                $sqlCmd = [System.Data.SqlClient.SqlCommand]::new($Query, $sqlConn)
                $sqlAdapter = [System.Data.SqlClient.SqlDataAdapter]::new($sqlCmd)
                $dataSet = [System.Data.DataSet]::new()
                [void]$sqlAdapter.Fill($dataSet)
                return $dataSet.Tables[0]        
            }
            catch {
                $LogAction.Invoke("Failed to execute query: $Query")                
                throw
            }
        }
    }
    end {
        $LogAction.Invoke('Closing connection')
        $sqlConn.Close()        
    }
 }