Returning an Output Parameter Using a DataReader

[ 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
Nội dung trích xuất từ tài liệu:
Returning an Output Parameter Using a DataReader [ Team LiB ]Recipe 2.9 Returning an Output Parameter Using a DataReaderProblemYou want to access an output parameter returned by a stored procedure that you haveused to create a DataReader.SolutionAdd a parameter to a Commands ParameterCollection and specify theParameterDirection 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 inExample 2-9. The stored procedure returns a single output parameter, and then the storedprocedure sets this value to the value of the input parameter specified by the user. Thecode displays the value of the output parameter at four different stages of working withthe 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 closedExample 2-9. Stored procedure: SP0209_OutputValueWithDataReaderCREATE PROCEDURE SP0209_OutputValueWithDataReader @ValueIn int, @ValueOut int outputAS set nocount on set @ValueOut = @ValueIn select * from Orders RETURNThe C# code is shown in Example 2-10.Example 2-10. File: SpOutputValueDataReaderForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Text;using System.Data;using System.Data.SqlClient;// . . .StringBuilder result = new StringBuilder( );// Create the connection.SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings[Sql_ConnectString]);// Create the command.SqlCommand cmd = new SqlCommand(SP0209_OutputValueWithDataReader, conn);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}result.Append(After reading all + rowCount + rows, output value = + outParam.Value + Environment.NewLine);// Close the DataReader.dr.Close( );result.Append(After DataReader.Close( ), output value = + outParam.Value + Environment.NewLine);// Close the connection.conn.Close( );resultTextBox.Text = result.ToString( );DiscussionOutput parameters allow a stored procedure to pass a data value or cursor variable backto the caller. To use an output parameter with a DataReader, add the output parameter tothe ParameterCollection for the Command object used to create the DataReader. Specifythe ParameterDirection property of the Parameter as Output or InputOutput. Table 2-11describes all values in the ParameterDirection enumeration. Once all parameters aredefined, build the DataReader using the ExecuteReader( ) method of the Commandobject. Table 2-11. ParameterDirection enumeration Value Description The parameter is an input parameter allowing the caller to pass a dataInput value to the stored procedure. The parameter is both an input and output parameter, allowing the callerInputOutput to pass a data value to the stored procedure and the stored procedure to pass a data value back to the caller. The parameter is an output parameter allowing the stored procedure toOutput 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 availableuntil the DataReader is closed by calling the Close( ) method or until Dispose( ) is calledon the DataReader. You do not have to read any of records in the DataReader to obtain anoutput value.[ Team LiB ]