Recipe 2.12 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: SpReturnValueDataReaderForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;

//  . . . 

StringBuilder result = new StringBuilder( );

// Create the connection.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);

// Create the command.
SqlCommand cmd = new SqlCommand("SP0212_ReturnValueWithDataReader", conn);
cmd.CommandType = CommandType.StoredProcedure;

// Define the input parameter for the command.
cmd.Parameters.Add("@ValueIn", SqlDbType.Int);
// Set the input parameter value.
cmd.Parameters["@ValueIn"].Value = Convert.ToInt32(returnValueTextBox.Text);

// Define the return parameter for the command.
SqlParameter retParam = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
retParam.Direction = ParameterDirection.ReturnValue;

result.Append("Before execution, return value = " + retParam.Value +
    Environment.NewLine);

// Open the connection and create the DataReader.
conn.Open( );
SqlDataReader reader = cmd.ExecuteReader( );

result.Append("After execution, return value = " + retParam.Value +
    Environment.NewLine);

// Iterate over the records for the DataReader.
int rowCount = 0;
while (reader.Read( ))
{
    rowCount++;

    // Code to process result set in DataReader.
}

result.Append("After reading all " + rowCount + " rows, return value = " +
    retParam.Value + Environment.NewLine);

// Close the DataReader.
reader.Close( );
result.Append("After DataReader.Close( ), return value = " +
    retParam.Value + Environment.NewLine);

// Close the connection.
conn.Close( );
result.Append("After Connection.Close( ), return value = " +
    retParam.Value);

resultTextBox.Text = result.ToString( );

Discussion

Every stored procedure returns an integer value to the caller. If the value for the return code is not explicitly set, it defaults to 0. The return value is accessed from ADO.NET through a parameter that represents it. The parameter is defined with a ParameterDirection property of ReturnValue (Table 2-13 describes all values in the ParameterDirection enumeration). The data type of the ReturnValue parameter must be set to Integer. Once all parameters are defined, build the DataReader using the ExecuteReader( ) method of the Command object.

Table 2-13. ParameterDirection enumeration

Value	Description
Input	The parameter is an input parameter allowing the caller to pass a data value to the stored procedure.
InputOutput	The parameter is both an input and output parameter, allowing the caller to pass a data value to the stored procedure and the stored procedure to pass a data value back to the caller.
Output	The parameter is an output parameter allowing the stored procedure to pass a data value back to the caller.
ReturnValue	The parameter represents the value returned from the stored procedure.

Return parameters from the stored procedure used to build a DataReader are not available until the DataReader is closed by calling the Close( ) method or until Dispose( ) is called on the DataReader. You do not have to read any of records in the DataReader to obtain a return value.