Returning an Output Parameter Using DataReader

To access an output parameter returned by a stored procedure that you have used to create a DataReader.

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 below example. 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: 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 below

Example: 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( );

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.