Output parameters undefined? Huh?

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);
}
}

--
- Jim Owen

re: Output parameters undefined? Huh?

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.

"Jim Owen" <jkoseattle@comcast.net> wrote in message
news:eDye%23vEmDHA.1764@tk2msftngp13.phx.gbl...[color=blue]
> 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[/color]
simple.[color=blue]
> 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[/color]
from[color=blue]
> a SqlCommand until after the DataReader is exhausted, but after that, it
> should be fine. The Stored Procedure, when run outside the application[/color]
(from[color=blue]
> within Sql Server), returns the correct values, so the fault lies[/color]
somewhere[color=blue]
> here.
>
> (The local methods GetInputParam and GetOutputParam simply build
> SqlParameter objects by setting the Direction, Name, Size if necessary,[/color]
and[color=blue]
> 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[/color]
and[color=blue]
> 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);
> }
> }
>
> --
> - Jim Owen
>
>[/color]

re: Output parameters undefined? Huh?

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.

"Jim Owen" <jkoseattle@comcast.net> wrote in message
news:eDye%23vEmDHA.1764@tk2msftngp13.phx.gbl...[color=blue]
> 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[/color]
simple.[color=blue]
> 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[/color]
from[color=blue]
> a SqlCommand until after the DataReader is exhausted, but after that, it
> should be fine. The Stored Procedure, when run outside the application[/color]
(from[color=blue]
> within Sql Server), returns the correct values, so the fault lies[/color]
somewhere[color=blue]
> here.
>
> (The local methods GetInputParam and GetOutputParam simply build
> SqlParameter objects by setting the Direction, Name, Size if necessary,[/color]
and[color=blue]
> 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[/color]
and[color=blue]
> 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);
> }
> }
>
> --
> - Jim Owen
>
>[/color]