Output parameters undefined? Huh?

[Jim Owen]

The following code does not operate properly, and neither I nor the three
..Net experts can figure out why. It's a seeming mystery. The code is simple.
A SqlCommand is executed against a stored procedure, then a DataReader is
looped through. After the DataReader is exhausted, I attempt to read some
output parameters from the SqlCommand. However, both the output parameters
return <undefined value>.

I understand that one cannot examine the contents of output parameters from
a SqlCommand until after the DataReader is exhausted, but after that, it
should be fine. The Stored Procedure, when run outside the application (from
within Sql Server), returns the correct values, so the fault lies somewhere
here.

(The local methods GetInputParam and GetOutputParam simply build
SqlParameter objects by setting the Direction, Name, Size if necessary, and
data type. They are used all throughout the application and do not pose
problems, unless perhaps some other property needs to be filled.
GetSqlCommand is another local method which creates a SqlCommand object and
assigns the SqlParameter array to it. Same story.)


SqlDataReader vDataReader;
SqlCommand vSqlCommand = null;

SqlParameter[] aryParams = new SqlParameter[3];
aryParams[0] = GetInputParam("@IncludeInactive",
SqlDbType.Bit);
aryParams[1] = GetOutputParam
("@ErrorMessage",SqlDbType.NVarChar, 512);
aryParams[2] = GetOutputParam ("@ReturnValue",
SqlDbType.Int);

vSqlCommand = GetSqlCommand("MyStoredProcedure",
aryParams);
vSqlCommand.Connection.Open();
vDataReader = vSqlCommand.ExecuteReader();

while (vDataReader.Read())
{
// code using parameters from the data set
residing in vDataReader (populating an object)
}

if
(vSqlCommand.Parameters["@ErrorMessage"].Value.ToString()!=""

||(int)vSqlCommand.Parameters["@ReturnValue"].Value == -1 )
{
throw new
CustomException(vSqlCommand.Parameters["@ErrorMessage"].Value.ToString(),
null);
}
}

 

[Rajesh.V]

This would work fine if you use a DataSet instead of the datareader. That is
if its not a big perf implication as Datasets are more heavy and slow than
the data reader.