get a returned value and a set of record in C# when calling a stored procedure

Question

I have a stored procedure that returns 2 output parameters and a record.

But in C#:

• ExecuteReader won't allow a returned value but allows records
• ExecuteNonQuery allows a returned value but not record.

How can I get both?

Answer 1

The output parameter returned by a stored procedure are not available until you close the DataReader.

Supposing you have

 SqlDataReader reader = cmd.ExecuteReader();
  ...... do you record reading
 reader.Close();

 // Now the output parameters are available
 int result = (int)cmd.Parameters["OutputParameter1"].Value;         

Of course this is supposing that you have correctly setup your output parameters....

This is from SqlDataReader docs on MSDN

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.