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.