Why does SqlDataReader behave differently with Set-PSDebug enabled?

We recently had someone ask why their PowerShell script stopped working correctly when they enabled tracing with the command Set-PSDebug -Trace 2.  The part of the code in question that didn't work basically boiled down to something like this:
function Execute-Query($connectionString) { $connection = New-Object System.Data.SqlClient.SQLConnection; $connection.ConnectionString = $connectionString; $connection.Open(); $cmd = $connection.CreateCommand(); $cmd.CommandText = "SELECT * FROM [dbo].[Table]"; $cmd.Connection = $connection; $result = $cmd.ExecuteReader(); $result } $result = Execute-Query $connString foreach($Record in $result) { $Record[1] }

When this was ran with Set-PSDebug -Trace 2 turned on, the foreach loop's body would not execute no matter how many records where returned by the query.  If Set-PSDebug -Off is then set and the code ran again, it would execute the body of the loop for each record that the query returned.

This happens because the output from Set-PSDebug -Trace 2 results in the object being generically enumerated before it gets to the foreach loop and the object only supports being enumerated through once.  It's also an issue to some degree because said object is assigned to a variable before that variable is then written to the pipeline which results in the need for more trace output.  Assigning the collection result to a variable, outputting that variable from the function, assigning to a second variable, and operating the foreach loop against that variable while having the tracing enumerate through the collection each time in order to output its contents is what ultimately causes this to result in different behavior with the tracing enabled versus disabled.  That is not supported to behave differently in that scenario and thus will not be changed to accommodate that scenario.  Most collections can be enumerated through multiple times so this won't come up with them, but if you do want to use Set-PSDebug and use classes which are enumerable but only once for a given object, you do have some options besides just turning Set-PSDebug off.

If you don't need to do the multiple variable assignments or have the function return the collection directly, you can output the $cmd object and only call ExecuteReader() when your code actually needs the result of the query.

If you're using PowerShell 4 or 5, this can be worked around by changing the lines "$result =$cmd.ExecuteReader(); $result" to "$result = $cmd.ExecuteReader().GetEnumerator(); $result" unless you need to use members of SqlDataReader that aren't present in its IEnumerator.  That isn't the only way to resolve it but it is the smallest code change that comes to mind while keeping the multiple variable assignments in tact.  You can see how the output differs in the trace output from Set-PSDebug -Trace 2 when calling ExecuteReader().GetEnumerator() versus ExecuteReader().  When just ExecuteReader() is assigned to the variable, the collection is enumerated through in the output and the type of the variable is SqlDataReader.  When ExecuteReader().GetEnumerator() is assigned to the variable,  just IEnumerator is seen in the trace output when the variable is set instead of the collections being enumerated through and the variable is of the type DbEnumerator.  If one doesn't need the assignment to $result in the function, simply outputting "$cmd.ExecuteReader()" to the pipeline also works in this situation.  If you do need to assign the SqlDataReader type to a variable to use its members but only from within the function itself and don't mind doing the query twice, you can assign $result to $cmd.ExecuteReader(), do what you need to do with $result, call $result.Close(), and then  call $cmd.ExecuteReader() again to output its result.

If the multiple variable assignments of the collection and working with Set-PSDebug -Trace 2 are necessary for one's scenario, one thing that you can do that works at least as far back as Powershell 2 is to wrap the enumerable results into another collection object which supports multiple enumerations.  For instance, instead of assigning the result of $cmd.ExecuteReader() directly to result, you could make $result = New-Object System.Collections.ArrayList and then pipe the ExecuteReader function's results to ForEach-Object { [void]$reader.Add($_) }.  That would result in an ArrayList containing the data record objects which could be enumerated through multiple times.  If you want to assign that collection to a variable to work with within a function before putting it to the pipeline from that function and using that variable in a foreach loop, you'll want to wrap that in another collection as well if Write-Output with the NoEnumerate option isn't available (e.g. your PowerShell version is older than 4).