It is not possible to return an object from one function and pass it to another

Question

I have a Powershell function that calls a stored procedure and ends with a call ExecuteReader. This returns an object, which is then passed to another function. It seems that the type of object is changing somewhere during this process. I suspect that I am invoking a method somewhere without intent.

I trimmed my script before 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 result Write-Output $reader.GetType():

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

(Note that changing the declared type of the argument $readerfrom System.Data.SqlClient.SqlDataReaderto System.Data.Common.DbDataReaderdoes not help.)

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

Answer 1

Powershell . Powershell , , return ,$myVar

. , , Powershell (, [int]$myInt = 7.

. Powershell .