[ Team LiB ]Recipe 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

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
}


Output
The parameter is an output parameter allowing the stored procedure to
pass a data value back to the caller.
ReturnValue The parameter represents the value returned from the stored procedure.
Output parameters from the stored procedure used to build a DataReader are not available
until the DataReader is closed by calling the Close( ) method or until Dispose( ) is called
on the DataReader. You do not have to read any of records in the DataReader to obtain an
output value.

[ Team LiB ]