Output parameters not readable when used with a DataReader

Question

When using a DataReader object to access data from a database (such as SQL Server) through stored procedures, any output parameter added to the Command object before executing are not being filled after reading. I can read row data just fine, as well as all input parameters, but not output ones.

[This question is actually just for anyone's future reference and help. It is a problem that's bitten us in the past and I thought I'd share it and the solution to anyone else that runs into this quirk.]

Answer 1

This is due to the "by design" nature of DataReaders. Any parameters marked as ParameterDirection.Output won't be "filled" until the DataReader has been closed. While still open, all Output parameters will more than likely just come back null.

The full Microsoft KB article concerning this can be viewed here.

Answer 2

If your Command contains output parameters or return values, they will not be available until the DataReader is closed.

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.