Unable to return an object from one function and pass it to another

Question

I have a Powershell function that invokes a stored procedure and finishes with a call to ExecuteReader. That returns an object which I then pass to another function. It appears that the type of the object changes somewhere during that process. I suspect that I am calling a method somewhere without intending to.

I've trimmed my script down to this:

Param(
    [string] $DatabaseHost,
    [int32] $RunA,
    [int32] $RunB
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

# This function works as expected.
Function New-DatabaseConnection {
    Param(
        [string] $databaseHost
    )
    $connectionProperties = @{}
    $connectionProperties.ConnectionString = "Server=$databaseHost;Database=fitbit;Integrated Security=True"
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection -Property $connectionProperties
    $connection.Open()
    return $connection
}

Function Invoke-StoredProcedure {
    Param(
        [int32] $runA,
        [int32] $runB
    )
    $command = $connection.CreateCommand()
    $command.CommandType = [System.Data.CommandType] 'StoredProcedure'
    $command.CommandText = 'analysis.compareRunsWithSameInputs'
    [void] $command.Parameters.Add('@runA', $runA)
    [void] $command.Parameters.Add('@runB', $runB)
    return $command.ExecuteReader()  # What happens between here and the call to Write-ResultSetToSheet?
}

Function Write-ResultSetToSheet {
    Param(
        [System.Data.SqlClient.SqlDataReader] $reader
    )
    # The body of this function is irrelevant, because we don't get this far.
    [void] $reader.Read()
    Write-Output $reader.GetString(0)
}

$connection = New-DatabaseConnection $DatabaseHost
try {
    $reader = Invoke-StoredProcedure $RunA $RunB
    Write-ResultSetToSheet $reader  # This fails, because somehow the type of $reader has changed from SqlDataReader to DataRecordInternal.
} finally {
    $connection.Close()
}

When I execute this, I get this error:

Write-ResultSetToSheet : Cannot process argument transformation on parameter 'reader'. Cannot convert the "System.Data.Common.DataRecordInternal" value of type "System.Data.Common.DataRecordInternal" to type "System.Data.SqlClient.SqlDataReader".
At C:\dev\ps1\Invoke-SoTest.ps1:45 char:28
+     Write-ResultSetToSheet $reader
+                            ~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Write-ResultSetToSheet], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : ParameterArgumentTransformationError,Write-ResultSetToSheet

Merging the two functions works though:

...
[void] $command.Parameters.Add('@runB', $runB)
$reader = $command.ExecuteReader()
Write-Output $reader.GetType()  # I'm using this to check the type of the object.  See below for more details.
[void] $reader.Read()
Write-Output $reader.GetString(0)

This is the output from Write-Output $reader.GetType():

IsPublic IsSerial Name                                     BaseType                                                                                                                                                                                              
-------- -------- ----                                     --------                                                                                                                                                                                              
True     False    SqlDataReader                            System.Data.Common.DbDataReader

(Note that changing the declared type of the $reader argument from System.Data.SqlClient.SqlDataReader to System.Data.Common.DbDataReader doesn't help.)

I'm an experienced developer, but new to .NET and very new to PowerShell.

Answer 1

When returning from a function Powershell will sometimes attempt to unroll the object. To force Powershell to not unroll an object use a comma in front of the variable being returned, return ,$myVar

This should help in moving objects around between functions. You may also want to make sure you are strongly typing objects if Powershell is having a hard time determining the object type, (e.g., [int]$myInt = 7.

Also see Powershell Operators for additional information.