Strange behavior in PowerShell function returning DataSet/DataTable

This is driving me crazy. I have a library I sourc开发者_如何学运维e 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.

---

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.

---

PowerShell special-cases the DataTable internally. It does not implement any of the usual suspect interfaces like ICollection, IList or IEnumerable which normally trigger the unrolling. You can dig into this a bit with:

PS> $dt = new-object data.datatable
PS> $dt -is [collections.ienumerable]
False

Yet:

PS> $e = [management.automation.languageprimitives]::GetEnumerator($dt)
PS> $e.gettype()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
False    False    RBTreeEnumerator                         System.ValueType

-Oisin

---

2 things you need to indeed focus on a) prepend your returned object with the comma indeed b) when you're filling your adaptor, make sure to either assign the outcome to a (disposalble) variable or do an Out-Null

I didn't do the Out-Null and even with a prepended comma, I kept getting a collection back (item 0= number of rows from the query, item1= the datatable) Drove my a bit crazy until I picked the Out-null parameter out.

Very weird IMHO, as I'm asking specifically to return the datatable but kept getting the collection back, even with the "," in front

function  Oracleconnection
{
  process
  {
  trap
    {
      Write-Host "error occured on oracle connection"
      Write-Host $_
      continue
    }
    [System.Reflection.Assembly]::LoadWithPartialName(“System.Data.OracleClient”) | out-null
    $connection = new-object system.data.oracleclient.oracleconnection( `
    "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.host)(PORT=1800)) `
    (CONNECT_DATA=(SERVICE_NAME=myservicename)));User Id=myid;Password=mypassword;");

    $query = "SELECT country, asset FROM table "
    $set = new-object system.data.dataset
    $adapter = new-object system.data.oracleclient.oracledataadapter ($query, $connection)
    $adapter.Fill($set) | Out-Null
    $table = new-object system.data.datatable
    $table = $set.Tables[0]
    return ,$table
  }
}

(Concepts from the Answer by Keith!)