DataReaders, Stored Procedures and Output parameters

If you're running a stored procedure via ExecuteReader and the stored procedure has some output parameters, you won't be able to access the values of the output parameters until you're done with the IDataReader. Thanks to Bill Vaughn for an MSDN article that cleared this up for me. See the section "Using a DataReader to Process the RETURN Value and OUTPUT Parameters". Note how he calls the DataReader's Close() method before accessing the output or return values.

3 Comments

  • I just ran into this issue today...quite annoying. Especially since, AFAICT, the DataReader also doesn't have a property showing the number of records returned - which is what I was using the output parameter for.

    After stumbling across a reference on Google on why I couldn't get my output parameter, I ended up just returning 2 result sets from the stored procedure.

  • Mark,



    The DataReader can't tell you how many records it returns since it streams the data to you on an as-needes basis: one row at a time.



    If you need a DataReader (for speed) and also want the number of records, then I think a combination of returning a recordset with the outut parameter holding the count would be the best way.

  • After reading the linked MSDN article, it appears a DataReader also returns the number of rows in the RecordsAffected property. Unfortunately, like output parameters, it's only available AFTER you reach the end of the recordset (or perhaps when you close the DataReader, I'm not clear on that).

    Unfortunately for me, I want the number of records BEFORE processing the recordset. The only way I could figure out to do that was to return 2 recordsets - the first with the number of records, and the second with the actual data. I can then get the number of records, and if it's > 0, call NextResultSet and process.

Comments have been disabled for this content.