Returning an OleDbDataReader from a function

Question

In an attempt to refactor some of my code, I've added a function to setup an OleDbDataReader.

The return value that I'm getting is for some odd reason a System.Data.Common.DbDataRecord.

Just prior to the function returning the object, I checked to see that it is indeed a DataReader.

Here's the function's code:

function Execute-Reader { param($conObj = $(throw "conObj parameter required"), $sqlStr = $(throw "sqlStr parameter required"))

$cmd = New-Object system.data.oledb.oledbcommand
$cmd.connection = $conObj
$cmd.commandtext = $sqlStr
$reader = $cmd.executereader()

return [System.Data.OleDb.OleDbDataReader]$reader 

}

and being called with the following

$reader = Execute-Reader $conObj $query

There's no exceptions. The only problem is with the $reader being the incorrect type. Somehow casted, or something.

Answer 1

What you're probably seeing is a behavior of PowerShell that automatically "unwraps" an object that implements IEnumerable. OleDbDataReader implements IEnumerable. In other words, when you return $Reader, you are really doing:

foreach ($record in $reader) { 
    write-output $record
}

To work around this, you can try doing the following. (Lose the cast as it's not necessary, I'm sure you just put it in as a sanity check anyway.)

Return ,$Reader

This will effectively create a single-element array which when unwrapped will return your original OleDbDataReader. This can be tricky sometimes and in my opinion it's certainly not intuitive. But once you realize what's going on it makes you start to think more explicitly about whether you're returning a single item or a collection of items.

But having said that, such a function seems like it would make more sense to have it return actual records via the pipeline instead of the reader anyway which the caller would then be responsible for enumerating and closing. It might be worthwhile to rethink the design of the function and do the while (reader.read()) stuff inside the function and have it write out a PSObject per row.