ADO.NET Cookbook

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