database - Strange behavior in PowerShell function returning DataSet/DataTable

Question

This is driving me crazy. I have a library I source from multiple scripts, which contains the following function:

function lib_open_dataset([string] $sql) {
    $ds = new-object "System.Data.DataSet"
    $da = new-object "System.Data.SqlClient.SqlDataAdapter" ($sql, $_conn_string)

    $record_count = $da.Fill($ds)

    return $ds
}

This is called pretty much everywhere and it works just fine, except that I normally have to do this:

$ds = lib_open_dataset($some_sql)
$table = $ds.Tables[0]
foreach ($row in $table.Rows) {
    # etc
}

So I created a new simple wrapper function to avoid the extra step of dereferencing the first table:

function lib_open_table([string] $sql) {
    $ds = lib_open_dataset $sql
    return $ds.Tables[0]
}

The problem is that what's being returned from here is the Rows collection of the table for some reason, not the table itself. This causes the foreach row loop written as above to fail with a "Cannot index into a null array." exception. After much trial and error I figured out this works:

foreach ($row in $table) {
    # etc
}

Note the difference between $table.Rows and just $table in the foreach statement. This works. Because $table actually points to the Rows collection. If the statement

return $ds.Tables[0]

is supposedly correct, why is the function returning a child collection of the table object instead of the table itself?

I'm guessing there's something in the way Powershell functions work that's causing this obviously, but I can't figure out what.

Answer 1

You can use the comma operator to wrap the rows collection in an array so that when the array is unrolled you wind up with the original rows collection e.g.:

function lib_open_table([string] $sql) {
    $ds = lib_open_dataset $sql    
    return ,$ds.Tables[0]
}

Essentially you can't prevent PowerShell from unrolling arrays/collections. The best you can do is workaround that behavior by wrapping the array/collection within another, single element array.