[ Team LiB ] Recipe Retrieving Stored Procedure Return Values Using a DataReader Problem You are using a stored procedure to create a DataReader and need to get the return value. When you try to access the value, it is null. How can you access the return value? | Team LiB Recipe Retrieving Stored Procedure Return Values Using a DataReader Problem You are using a stored procedure to create a DataReader and need to get the return value. When you try to access the value it is null. How can you access the return value Solution Use a parameter defined with a ParameterDirection property of ReturnValue. The sample code uses a single stored procedure as shown in Example 2-14 SP0212_ReturnValueWithDataReader Returns a result set containing all records from the Orders table in Northwind. The stored procedure takes a single input parameter which it simply returns. Example 2-14. Stored procedure SP0212_ReturnValueWithDataReader CREATE PROCEDURE SP0212_ReturnValueWithDataReader @ValueIn int 0 AS set nocount on select from Orders RETURN @ValueIn The sample code creates a DataReader from a stored procedure command. The stored procedure returns the value of the single input parameter specified by the user. The code displays the value of the return parameter at five 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 After the Connection is closed The C code is shown in Example 2-15. Example 2-15. 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 SP0212_ReturnValueWithDataReader conn Define the input parameter for the command. @ValueIn Set the input parameter value. @ValueIn .Value Define the return parameter for
