ExecuteReader and the missing output parameters

Most of the time, I like to get some indicator of success back from the stored procedures that I write. To do this, I will add return parameters and sometimes, if I am doing parameter validation, will use output parameters to provide individual values that do not belong to the result set as a whole.

Recently, when building a business layer for an application I began testing the output returned from the output parameters and return value parameters. Since I was simply populating the objects from data in the database and caching the results within the application, I wanted to use the DataReader to retrieve results from the database. I did this because I know that the DataSet carries a lot of unnecessary weight around that wasn't needed in this solution.

So, with DataReader in hand, I began testing some of the data access code. I was rewarded with DBNull results when I tried to access the ReturnValue parameter value or any of my output parameter values.

I began by defining the parameters for the stored procedure (Using the Enterprise Library Data Access Block):

db.AddOutParameter(cmd, "Output", DbType.String, 10);
db.AddParameter(cmd, "Return_Value", DbType.Int32,
ParameterDirection.ReturnValue, string.Empty,
DataRowVersion.Current, 0);

This created an output parameter named "Output" and a return parameter named "Return_Value". Then, I ran the following code to retrieve the results and access the parameter values .

DbDataReader reader = null;
try
{
reader = db.ExecuteReader(cmd);
string output = (string)db.GetParameterValue(cmd, "Output");
int iResult = (int)db.GetParameterValue(cmd, "Return_Value");
if (iResult != 0)
{
// handle error in executing stored procedure 
}
else
{
while (reader.Read())
{
// do something with results rows
}
}
}
catch ( DbException ex )
{
// handle errors in executing stored procedure
}
catch ( Exception ex )
{
// handle errors in getting return values
}
finally
{
if ( reader != null ) reader.Close();
}

However, when I attempted to access the parameter values after executing the data reader, I would hit an InvalidCastException. I first looked at the stored procedure and made sure that the values were getting passed in correctly and ran SQL Profiler to assure that the stored procedure was executing properly. After validating, I checked the parameter values and found that they were not invalid types, but DBNull, which cannot be cast to any other value.

Trying to determine what was happening, I changed the default values of the parameters when I defined them, but would always get the default value after execution. Frustrated with this process, I decided to try using a DataSet to execute the query. When I did so, my parameter values "magically" appeared and everything worked as excepted.

I have not had time to delve into the System.Data.SqlClient code to see why those values are not set, but have determined that if I want to use output parameters and return value parameters, then I will have to pay the piper and use the DataSet, with all of its weight and ungainliness. What a pain. Has anyone else been able to get output parameters to work using the ExecuteReader method? Let me know, as I would love to drop the DataSet, since I don't need it.