Recipe 2.9 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: SpOutputValueDataReaderForm.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("SP0209_OutputValueWithDataReader", 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[0].Value = Convert.ToInt32(outputValueTextBox.Text);

// Define the output parameter for the command.
SqlParameter outParam = cmd.Parameters.Add("@ValueOut", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;

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

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

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

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

    //  . . .  Code to process result set in DataReader
}

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

// Close the DataReader.
dr.Close( );
result.Append("After DataReader.Close( ), output value = " +
    outParam.Value + Environment.NewLine);

// Close the connection.
conn.Close( );

resultTextBox.Text = result.ToString( );

Discussion

Output parameters allow a stored procedure to pass a data value or cursor variable back to the caller. To use an output parameter with a DataReader, add the output parameter to the ParameterCollection for the Command object used to create the DataReader. Specify the ParameterDirection property of the Parameter as Output or InputOutput. Table 2-11 describes all values in the ParameterDirection enumeration. Once all parameters are defined, build the DataReader using the ExecuteReader( ) method of the Command object.

Table 2-11. 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.

Output 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 an output value.