ADO.NET Cookbook

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; ...