ADO.NET 3.5 Cookbook, 2nd Edition

3.22. Retrieving a Return Value from a Stored Procedure

Problem

You are using a stored procedure and need to get the return value.

Solution

Use a parameter defined with a ParameterDirection property of ReturnValue and access the return value.

The solution uses a single stored procedure:

Person.GetContacts

Returns a result set containing all records in the Person.Contact table in AdventureWorks. Also, the stored procedure returns the @@ROWCOUNT value for the query in an output parameter. The stored procedure is shown in Example 3-30.

Example 3-30. Stored procedure: Person.GetContacts

CREATE PROCEDURE Person.GetContacts
    @RowCount int OUTPUT
AS
    SET NOCOUNT ON

    SELECT * FROM Person.Contact

    SET @RowCount = @@ROWCOUNT

    RETURN @RowCount

The solution creates a DataReader from the stored procedure command. The stored procedure returns the count of records in the return result set—in this case, all records from the Person.Contact table in AdventureWorks. 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 in Program.cs in the project StoredProcedureReturnValueDataReader is shown in Example 3-31.

Example 3-31. File: Program.cs for StoredProcedureReturnValueDataReader solution

using System; using System.Data; using System.Data.SqlClient; namespace ...