[ Team LiB ] Recipe Returning an Output Parameter Using a DataReader Problem You want to access an output parameter returned by a stored procedure that you have used to create a DataReader. Solution Add a parameter | Team LiB Recipe Returning an Output Parameter Using a DataReader Problem You want to access an output parameter returned by a stored procedure that you have used to create a DataReader. Solution Add a parameter to a Command s ParameterCollection and specify the ParameterDirection as either Output or InputOutput. The sample code uses a single stored procedure SP0209_OutputValueWithDataReader Returns a result set containing all records from the Orders table in Northwind. The stored procedure takes one input and one output parameter and sets the value of the output parameter to the value of the input parameter. The sample code creates a DataReader from a stored procedure command as shown in Example 2-9. The stored procedure returns a single output parameter and then the stored procedure sets this value to the value of the input parameter specified by the user. The code displays the value of the output parameter at four different stages of working with the result set in the DataReader Before the DataReader is created Immediately after the DataReader is created After all rows in the DataReader have been read After the DataReader is closed Example 2-9. Stored procedure SP0209_OutputValueWithDataReader CREATE PROCEDURE SP0209_OutputValueWithDataReader @ValueIn int @ValueOut int output AS set nocount on set @ValueOut @ValueIn select from Orders RETURN The C code is shown in Example 2-10. Example 2-10. File Namespaces variables and constants using System using using using using . . . StringBuilder result new StringBuilder Create the connection. SqlConnection conn new SqlConnection Sql_ConnectString Create the command. SqlCommand cmd new SqlCommand SP0209_OutputValueWithDataReader conn Define the input parameter for the command. @ValueIn Set the input parameter .
