A helper script for query to MSSQL by PowerShell

Query.ps1

<##
 # Query.ps1
 #
 # (c) 2016 koohq. Licensed under CC0.
 # https://creativecommons.org/publicdomain/zero/1.0/legalcode
 #>

function Use
{
    [CmdletBinding()]
    [OutputType()]
    param(
        [Parameter(mandatory)]
        [IDisposable]$obj,
        [Parameter(mandatory)]
        [scriptblock]$sb
    )
    try
    {
        & $sb
    }
    finally
    {
        if ($null -ne $obj)
        {
            $obj.Dispose()
        }
    }
}

function Query-TSQL
{
    [CmdletBinding()]
    [OutputType([PSCustomObject[]])]
    param(
        [Parameter(mandatory)]
        [string]$ConnStr,
        [Parameter(mandatory)]
        [string]$Sql
    )

    Use ($conn = New-Object System.Data.SqlClient.SqlConnection($ConnStr)) {

        $conn.Open()

        Use ($cmd = New-Object System.Data.SqlClient.SqlCommand($Sql, $conn)) {

            Use ($reader = $cmd.ExecuteReader()) {

                $rows = @()
                $colNames = @()

                while ($reader.Read())
                {
                    if ($colNames.Length -eq 0) { $colNames = 0..($reader.FieldCount - 1) | foreach { $reader.GetName($_) } }
                    $row = [PSCustomObject]@{}
                    $colNames | foreach { $row | Add-Member -NotePropertyName $_ -NotePropertyValue $reader[$_] }
                    $rows += $row
                }

                $rows
            }
        }
    }
}