Invoke a parameterized query through PowerShell using System.Data and a HashTable of parameters, returns System.Data.DataTable

Invoke-ParamQuery.ps1

Function Invoke-ParamQuery {
    param(
        [String]$Query,
        $Parameters=@{},
        [Data.SqlClient.SqlConnection]$Conn,
        [int]$Timeout=3,
        [switch]$CloseConn
    )
    if ($conn.State -eq "Closed") {
        $conn.Open()
    }

    $cmd = New-Object System.Data.SqlClient.SqlCommand($Query,$Conn)
    $cmd.CommandTimeout = $Timeout

    ForEach ($p in $Parameters.Keys) {
        [void]$cmd.Parameters.AddWithValue("@$p",$Parameters[$p])
    }

    $dt = New-Object System.Data.DataTable
    $da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
    $da.Fill($dt) | Out-Null

    If ($CloseConn -and $conn.State -eq "Open") {
        $conn.Close()
        $conn.Dispose()
    }

    return $dt
}

<# Example

  $conn = New-Object Data.SqlClient.SqlConnection "Server=SQL-Something;Initial Catalog=DatabaseName;Integrated Security=True"
  $query = "
    SELECT UserID, UserName
    FROM Users
    WHERE AccessTypeID = @AccessType
      AND IsActive = @IsActive
  "
  Invoke-ParamQuery -Query $query -Parameters @{AccessType=1;IsActive=0} -Conn $conn -CloseConn

#>