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 createdImmediately after the
DataReader
is createdAfter all rows in the
DataReader
have been readAfter the
DataReader
is closedAfter 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 ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.