Why does Powershell think I'm trying to return an object[] rather then a DataTable?

I don't know about the other issues people are bringing up, but I can think of two possible reasons why you're getting back an Object[].

1. It might be that you have uncaptured output somewhere else in the function. In your case, Fill returns an int. Try adding this to the end of your function calls:

   | Out-Null

   E.g.,

   $adapter.Fill($dt) | Out-Null

   If any statement returns a value and you're not capturing it, the output will be included in your return value, and since you'll have multiple return values at that point, PowerShell will stuff them all into an array.

2. The other possibility is that PowerShell converts returned collections of all sorts into Object[]s. Use the , operator to return the unmangled value:

   return , $dt

   The , creates an array containing only the value that follows. As near as I can guess, this causes PowerShell to automatically unroll the outermost array and leave the value alone, since the actual return value is now an array that only contains a single value.

   The return is actually optional, since uncaptured values are included in the return value.

   Just ran into this one myself yesterday. I can't for the life of me figure out why anyone would think silently converting to Object[] is useful.

You do have a couple of issues in what you posted. Since you explicitly create a DataTable object with New-Object System.Data.DataTable, there is no reason to also coerce it to the DataTable type with [System.Data.DataTable]. The same is true for when you call the function. You are returning a DataTable object, so that is what you will get back. Also if you did want to specify the variable type, there should not be a space between the type identifier and the variable name. Regardless, these issues would not cause the behavior that you are seeing. I ran this code:

function GetBuildData {    $dt = New-Object System.Data.DataTable    $column1 = New-Object system.Data.DataColumn 'Col1'    $column2 = New-Object system.Data.DataColumn 'Col2'    $dt.columns.add($column1)    $dt.columns.add($column2)    $row = $dt.NewRow()    $row.col1 = '1'    $row.col2 = '2'    $dt.rows.add($row)    return $dt}$buildData = GetBuildData$buildDataCol1                                                                     Col2----                                                                     ----1                                                                        2

And it worked just fine. I suspect that the bit that is causing your problem is probably in the bit that you excluded for brevity. Shortcuts rarely are and you rarely get accurate answers from partial data.

A contributing factor is that you're not actually calling the open and close methods, but referring to them instead. PowerShell is outputting a reference to these methods as well as the datatable (which I imagine isn't actually populated due to the fact that the connection isn't open).

You need to include the parentheses on the open/close methods like this:

$conn.Open()...$conn.Close()

You also need to beware of methods which return values (.Add() methods are notorious for this) that you're not consuming either by assigning to a variable or piping to out-null.