Today, a query needs to return a result set through ExcuteReader, and at the same time it wants to output parameters. At the beginning, I could not get the value of the output parameters, thinking that the stored procedure had an error. However, the test in the query analyzer was correct, and the output parameters were indeed changed. Assignment.
What is even more puzzling is that after throwing an exception during the forced type conversion of the output, it can indeed be obtained again. Is it a bug of ado.net? It is impossible to imagine the page. It is impossible for such a commonly used API to appear. Is this wrong? My code is similar to the following scenario:
try {
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
int val = (int)cmd.Parameters[1].Value; // Still empty now
// more
}
}
catch(Exception exp) {
throw new ApplicationException("Output parameter value: " + cmd.Parameters[1].Value, exp); // Now the output value can be obtained
}
It’s really frustrating. After debugging and tracking for an hour,
I finally found the answer in MSDN:
When you use a Command object with a stored procedure, you can set the CommandType property of the Command object to StoredProcedure. When the CommandType is StoredProcedure, you can use the Command's Parameters property to access input and output parameters and return values. Regardless of which Execute method is called, the Parameters property can be accessed. However, when ExecuteReader is called, the return value and output parameters will not be accessible until the DataReader is closed.
ref: http://msdn2.microsoft.com/zh-CN/library/tyy0sz6b.aspx
That’s it. I feel like I’ve been fooled by MS again. Come to think of it, why am I inexperienced in learning? I’ve already changed the document and
returned to it. Your own code environment can still be explained.
Because when the Exception is caught, the using scope has been jumped out, the DataReader has been automatically closed, and the value of the output parameter can naturally be obtained.
Of course, if you put the try catch in using, you still can't get it, because it is still in the using scope and the DataReader has not been closed.
In addition, MSDN says that it can only be accessed if the DataReader is closed, but this is not the case.
After testing, it can be summarized as follows:
1. For ExecuteReader, Output parm and returnvalue are returned to DataReader as a result set, and the result set is always the last one.
2. According to 1, when there is a result set, to access the output parameters and return values, you need to call NextResult to the result set location corresponding to the output parameters and return values.
3. According to 1, when Execute does not return a result set, it can be accessed directly (note that there is no need to call Read())
4. Pay special attention to the need to call NextResult multiple times if multiple result sets are returned; if the number of result sets is dynamic, then the return value of nextResult() is false.
5. Even if the option CommandBehavior.SingleResult is specified for ExecuteReader (returning a single result set, in fact, returning the first result set of the batch), the output parameters are returned as a result set.
6. On closing the DataReader (Close()), the output parameters are populated and therefore accessible.
7. Since the DataReader is read-forward only, even if the output parameters are obtained through the NextResult method before closing the DataReader, the previous result set cannot be accessed anymore (in some cases, you may want to dynamically control the result set through the output parameters. visit).
8. In order to solve the problem in 6, you can not use the output parameter and directly return the output parameter as the first result (SELECT @parmname).
The above is just my own summary. I hope there is no discrepancy and it may be helpful to beginners.
ref:
http://www.bigcircleboy.net/583a194f-2c2c-4662-9036-4e2f0eb262396084313157728108.html