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.
Published Wednesday, November 24, 2004 8:45 PM by PSteele

Comments

# re: DataReaders, Stored Procedures and Output parameters

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.

Thursday, November 25, 2004 6:21 AM by Mark

# re: DataReaders, Stored Procedures and Output parameters

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.

Thursday, November 25, 2004 7:41 AM by Patrick Steele

# re: DataReaders, Stored Procedures and Output parameters

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.

Thursday, November 25, 2004 8:32 AM by Mark

# DataReaders output parameter Problem

This article clear all to me but I have a problem now .

I have  to now the output parameter before reader close but I can't run command.Executenonquery or command.ExecuteScalar because the speed for me is very important.

hoe can i do this.

Sunday, April 22, 2007 8:01 AM by arman

# DataReaders output parameter Problem

This article clear all to me but I have a problem now .

I have  to now the output parameter before reader close but I can't run command.Executenonquery or command.ExecuteScalar because the speed for me is very important.

how can i do this?

Sunday, April 22, 2007 8:01 AM by arman