Problems with output parameters in Excutereader

Problem

Today, a query needs to return the result set through Excutereader, but also want to output parameters, at the beginning of the time has not been the output parameter value, the stored procedure error, but in the Query Analyzer test is correct, and the output parameter has indeed been assigned value.
More people baffled is that the output of the mandatory type conversion thrown out of the exception, you can also get, is it ado.net bug, imagine the page is not possible ah, so commonly used APIs, it is impossible to make such a mistake, my code similar to the scene:

try {
using (SqlDataReader dr = cmd. ExecuteReader (commandbehavior.closeconnection)) {
int val = (int) cmd. PARAMETERS[1]. Value; It's still a null value.
More
}
}
catch (Exception exp) {
throw new ApplicationException ("Output parameter value:" + cmd.) PARAMETERS[1]. Value, exp); Now we can get the output value.
}

It's a really depressing, one-hour debug trail.

Finally, I found the answer in MSDN:
When you use a command object for a stored procedure, you can set the Command object's CommandType property to StoredProcedure. When CommandType is StoredProcedure, you can use the Command's Parameters property to access input and output parameters and return values. You can access the Parameters property regardless of which Execute method is invoked. However, when ExecuteReader is invoked, the return value and output parameters cannot be accessed until DataReader is closed.

Ref:http://msdn2.microsoft.com/zh-cn/library/tyy0sz6b.aspx

So Ah, think again by Ms Fooled, want to, who call their own apprentice Ah, and early change to check the document

Back to your own code environment, you can still explain.
Because when the catch to the expception has jumped out of the using range, the DataReader has been automatically shut down, nature can get output parameters of the value.
Of course, if you put a try catch in a using, you can't get it, because the DataReader is not closed because it is still in the using scope.

In addition, MSDN says that only close DataReader can be accessed, but it's not.
After testing, you can summarize the following:
1. For ExecuteReader, Output Parm and ReturnValue return DataReader as a result set, and the result set is always the last one.
2. According to 1, when there are result sets, to access the output parameters and return values, you need to call the result set position of the NextResult to the output parameter and the return value.
3. According to 1, when execute does not return a result set, it can be accessed directly (note that you do not need to call Read ())
4. Special note for returning multiple result sets, calls need to be invoked multiple times nextresult; If the number of result sets is dynamic, then the return value of NextResult () is false.
5. The output parameter is returned as a result set, even if the option Commandbehavior.singleresult (returning a single result set, which returns the first result set of the batch) is specified for ExecuteReader.
6. Turning off DataReader (Close ()) fills out the output parameters and can therefore be accessed.
7. Since DataReader is read-only forward, the previous result set cannot be accessed even by asking the output parameters through the NextResult before closing DataReader (in some cases, you might want to dynamically control the access of the result set through the output parameters).
8. To resolve the problem in 6, you can return the output parameter as the first result without using an output parameter (SELECT @parmname)

Above is only, own summary, hoped does not have the discrepancy, may have the help to the beginner.

Ref
Http://www.bigcircleboy.net/583a194f-2c2c-4662-9036-4e2f0eb262396084313157728108.html