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

Question

I have the following PS function:

function GetBuildData {
    [System.Data.SqlClient.SqlConnection] $conn = New-Object System.Data.SqlClient.SqlConnection
    [System.Data.SqlClient.SqlCommand] $cmd = New-Object System.Data.SqlClient.SqlCommand
    [System.Data.SqlClient.SqlDataAdapter] $adapter = New-Object System.Data.SqlClient.SqlDataAdapter
    [System.Data.DataTable] $dt = New-Object System.Data.DataTable
    
    try {
        [string] $connStr = "myConnectionString"
        
        $conn.ConnectionString = $connStr
        $cmd.Connection = $conn
        $cmd.CommandText = "SELECT * FROM TestTable"
        
        $conn.Open
        
        $adapter.SelectCommand = $cmd
    
        $adapter.Fill($dt)
        
        $conn.Close
    }
    catch [system.exception]
    {
        Write-Host $_
    }
    finally {
        $adapter.Dispose
        $cmd.Dispose
        $conn.Dispose
    }
    
    return $dt
}

Most of the function has been removed for brevity. The problem I have is when I call the function like so:

[System.Data.DataTable] $buildData = GetBuildData

I get the following error:

Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Data.DataTable".

I've already double-checked, and $dt does contain data. For example, the number of Rows.Count is 1 which is expected. Why does Powershell think that I'm wanting an object[] array returned when it's obvious that the $dt variable is a DataTable?

Answer 1

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.