Invoke-ParameterisedSqlQuery.ps1

function Invoke-ParameterisedSqlQuery {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [string] $ConnectionString,
        [Parameter(Mandatory)]
        [string] $Query,
        [Parameter(ValueFromPipeline)]
        [PSCustomObject[]] $QueryArgs
    )
    begin {
        Add-Type -AssemblyName System.Data.SqlClient | out-null
        $connection = [System.Data.SqlClient.SqlConnection]::new($ConnectionString)
        $connection.Open() | out-null
        $command = $connection.CreateCommand()
        $command.CommandText = $Query
        $parameters = @{}
        $first = $true
    }
    process {
        $QueryArgs |
            ForEach-Object {
                $argSet = $_

                if ($first) {
                    $first = $false
                    foreach ($key in $argset.keys) {
                        $parameter = $command.createparameter()
                        $parameter.parametername = $key
                        $parameter.value = $argset[$key]
                        $command.parameters.add($parameter) | out-null
                    }
                } else {
                    foreach ($key in $argset.keys) {
                        $parameter = $parameters[$key]
                        $parameter.value = $argset[$key]
                    }
                }
                
                $reader = $command.ExecuteReader()

                while ($reader.Read()) {
                    $row = @{}
                    0..($reader.FieldCount - 1) |
                        ForEach-Object {
                            $row[$reader.GetName($_)] = $reader[$_]
                        }
                    [pscustomobject]$row
                }
            }
    }
    end {
        $connection.Close() | out-null
    }
}

Test-InvokeParameterisedSqlQuery.ps1

. .\Invoke-ParameterisedSqlQuery.ps1

Invoke-ParameterisedSqlQuery "server=(local);database=CustomerDb;trusted_connection=true;" "select * from Team where Id > @Id;" @{ Id = 2 } | ft -auto
Invoke-ParameterisedSqlQuery "server=(local);database=CustomerDb;trusted_connection=true;" "select * from Team;" | ft -auto