Skip to content

Instantly share code, notes, and snippets.

@nbarnwell
Created March 12, 2022 14:13
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save nbarnwell/88b55dd89c0f651c251bda46d9090638 to your computer and use it in GitHub Desktop.
Save nbarnwell/88b55dd89c0f651c251bda46d9090638 to your computer and use it in GitHub Desktop.
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
}
}
. .\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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment