Posted on 2006-06-07 09:55
Programmer 阅读(1458)
评论(4) 编辑 收藏 网摘 所属分类:
.Net
SqlCommand.ExecuteReader & ParameterDirection.ReturnValue
I've made a great mistake when using the SqlCommand.ExecuteReader method. As we know, the ParameterDirection.ReturnValue parameters cannot be accessed if the associated SqlDataReader is still open. So, the return value will always be zero by calling SqlDataProviderBase.RunDataReader(string procName, SqlParameter[] parameters, out int returnValue).
I wrote a new class to resolve this bug. This class was named SqlDataReaderHolder.
- public sealed class SqlDataReaderHolder : IDisposable
- {
- internal SqlDataReaderHolder(SqlDataReader reader) : this(reader, null)
- {}
-
- internal SqlDataReaderHolder(SqlDataReader reader, SqlParameter param)
- {
- if ( reader == null ) {
- throw new ArgumentNullException("reader");
- }
- if ( param == null ) {
- _allowRequestReturnValue = false;
- }
- else {
- if ( param.Direction != ParameterDirection.ReturnValue ) {
- throw new ArgumentException(
- "Invalid sql parameter. It should be of return value type.",
- "param");
- }
- _allowRequestReturnValue = true;
- }
- _reader = reader;
- _parameter = param;
- }
-
- /// <summary>
-
- /// </summary>
- public void Dispose()
- {
- if ( !_disposed ) {
- _disposed = true;
-
-
- _reader.Dispose();
- _reader = null;
-
- if ( _allowRequestReturnValue ) {
-
- object value = _parameter.Value;
- if ( value != null && !value.Equals(DBNull.Value) ) {
- _returnValue = (int) _parameter.Value;
- }
- }
- }
- }
-
- /// <summary>
-
- /// </summary>
- public SqlDataReader Reader
- {
- get { return _reader; }
- }
-
- /// <summary>
-
- /// </summary>
- /// <value>
-
- /// </value>
- /// <exception cref="InvalidOperationException">
-
-
-
- /// </exception>
- public int ReturnValue
- {
- get
- {
- if ( !_allowRequestReturnValue ) {
- throw new InvalidOperationException("No return value is found.");
- }
- if ( !_disposed ) {
- throw new InvalidOperationException(
- "You cannot get the return value, since the associated SqlDataReader has not been closed.");
- }
- return _returnValue;
- }
- }
-
- private SqlDataReader _reader;
- private SqlParameter _parameter;
- private int _returnValue = -1;
- private bool _disposed;
- private bool _allowRequestReturnValue;
- }
The SqlDataProviderBase.RunDataReader method has to be rewritten too.
- protected SqlDataReaderHolder RunDataReader(string procName, SqlParameter[] parameters, bool requireReturnValue)
- {
- SqlConnectionHolder connectionHolder;
- SqlCommand sqlCommand;
- SqlDataReader dr;
-
-
- using ( connectionHolder = GetConnectionHolder() ) {
- connectionHolder.AutoClose = false;
-
-
- using ( sqlCommand = BuildSQLCommand(
- connectionHolder, procName, parameters, requireReturnValue) ) {
- if ( _underTrans ) {
-
-
- dr = sqlCommand.ExecuteReader(CommandBehavior.Default);
- }
- else {
-
- dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
- }
-
- if ( requireReturnValue ) {
- return new SqlDataReaderHolder(dr, sqlCommand.Parameters[s_returnValueParamName]);
- }
- else {
- return new SqlDataReaderHolder(dr);
- }
- }
- }
- }
Sample code:
- SqlDataProviderBase dataProvider;
- SqlDataReaderHolder holder;
- SqlParameter[] parameters;
-
- parameters = new SqlParameter[]{
- CreateInputParam("@myParam", SqlDbType.Int, 10),
- ...
- };
-
- using( holder = RunDataReader("dbo.myStoredProc", parameters, true) ){
- while( holder.Reader.Read() ){
-
- }
- }
-
- Console.Write( "The return value is {0}.", holder.ReturnValue );
-