ADO.NET 3.5 Cookbook, 2nd Edition

3.23. Retrieving a Stored Procedure Output Parameter

Problem

You want to access an output parameter returned by a stored procedure.

Solution

Add a parameter to a Command's ParameterCollection and specify the ParameterDirection as either Output or InputOutput.

The example uses a single stored procedure, shown in Example 3-32:

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.

Example 3-32. 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 based on the stored procedure. The stored procedure returns a single output parameter containing 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 output 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 StoredProcedureOutputValueDataReader is shown in Example 3-33.

Example 3-33. File: Program.cs for StoredProcedureOutputValueDataReader solution

using System; using System.Data; using ...