Why does Invoke-Sqlcmd -OutputAs DataTables return different types for one vs many records

user_139698 0
Viewed 102

PowerShell 5.1

$tblOutput = Invoke-Sqlcmd ... -OutputAs DataTables

Why does Invoke-Sqlcmd return a DataRow type when 1 record is return from database vs DataTable when greater than 1?

I would have expected that it returns DataTable regardless.

$successResults = $tblOutput.where({ $_.Flags -eq 36 -And $_.Result -eq 1 })

Preceding gets an error saying where is not defined on DataRow

datatable powershell sql-server
1 Answers

Seems like an unfortunate bug which can be reproduced with the following example:

$result = & {
    $dtt = [System.Data.DataTable]::new()
    $dtt.Columns.AddRange(@('Name', 'Value'))
    $row = $dtt.NewRow()
    $row.Name  = "Hello"
    $row.Value = "World"
    $dtt.Rows.Add($row)
    $dtt.GetType() | Out-Host # => DataTable
    $dtt
}

$result.GetType() # => DataRow

However as a workaround, you can add this DataRow to a new DataTable:

$dtt = [System.Data.DataTable]::new()
$dtt.Columns.AddRange(@($result.Table.Columns.ColumnName))
$dtt.Rows.Add($result.ItemArray)
$dtt.where{ $_.Name -eq 'Hello' }

Not a bug! Just PowerShell trying to enumerate the output from the script block. Since there is only one element in the DataTable, the return type is DataRow.

Another way to reproduce it:

$dtt = [System.Data.DataTable]::new()
$dtt.Columns.AddRange(@('Name', 'Value'))
$row = $dtt.NewRow()
$row.Name  = "Hello"
$row.Value = "World"
$dtt.Rows.Add($row)
$dtt.GetType() # => DataTable
$(foreach($i in $dtt) { $i }).GetType() # => DataRow

Or even shorter, as mklement0 points out in his helpful comment, since $( ) already enumerates, the foreach loop is not needed:

$($dtt).GetType() # => DataRow
user_153395440
Related