DataReader.RecordsAffected returning -1

本文关键字:returning RecordsAffected DataReader | 更新日期: 2023-09-27 18:15:24

当使用Oracle.DataAccess.Client执行以下代码时,我得到RecordsAffected -1;即使关闭数据阅读器。但是RecordsAffected应该是1。请帮我改正这个问题。

过程包含一条选择语句,插入或更新操作并返回值。

using( OracleConnection conn = new OracleConnection( ConnectionDataContext.SetConnection( ) ) )
            {
                conn.Open( );
                OracleCommand cmd = new OracleCommand( );
                cmd.Connection = conn;
                if( list.MapId == -1 )
                {
                    cmd.CommandText = SpSaveMap;
                }
                else
                {
                    cmd.CommandText = SpUpdateMap;
                }
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter [ ] OracleParmeter = new OracleParameter [ 6 ];
                OracleParmeter [ 0 ] = new OracleParameter( "ID", list.MapId );
                OracleParmeter [ 0 ].OracleDbType = OracleDbType.Int32;
                OracleParmeter [ 0 ].Direction = ParameterDirection.InputOutput;
                OracleParmeter [ 1 ] = new OracleParameter( "NAME", list.Name.Trim( ) );
                OracleParmeter [ 1 ].OracleDbType = OracleDbType.Varchar2;
                OracleParmeter [ 2 ] = new OracleParameter( "MAP_WIDTH", list.Width );
                OracleParmeter [ 2 ].OracleDbType = OracleDbType.Double;
                OracleParmeter [ 3 ] = new OracleParameter( "MAP_HEIGHT", list.Height );
                OracleParmeter [ 3 ].OracleDbType = OracleDbType.Double;
                OracleParmeter [ 4 ] = new OracleParameter( "MAP_THEME", list.Theme.Trim( ) );
                OracleParmeter [ 4 ].OracleDbType = OracleDbType.Varchar2;
                OracleParmeter [ 5 ] = new OracleParameter( "MAP_OBJECT_COUNT", list.ObjectCount );
                OracleParmeter [ 5 ].OracleDbType = OracleDbType.Int32;
                foreach( OracleParameter sqlper in OracleParmeter )
                    cmd.Parameters.Add( sqlper );
                OracleDataReader dataReader = cmd.ExecuteReader( );
                if( dataReader.RecordsAffected > 0 )
                {
                    status.ProcedureStatus = true;
                }
               dataReader.Close( );
               status.ReturnValue = cmd.Parameters [ "ID" ].Value.ToString( );
               if( status.ReturnValue == "-2" )
               {
                   status.ProcedureStatus = false;
                   status.ErrorMessage = "Map name is already exist.";
               }
               else
               {
                   status.ProcedureStatus = true;
               }
                cmd.Parameters.Clear( );
                cmd.Dispose( );
                conn.Close( );
            }
PROCEDURE  SP_UPDATE_MAP (
  id IN OUT map.map_id%type, 
  name map.map_name%type,
  map_width map.width%type,
  map_height map.height%type,
  map_theme map.theme%type,
  map_object_count map.object_count%type
  )
  AS
  exist number;
   BEGIN
   PKG_TMS_GET.SP_GET_UPDATE_EXIST_MAP_NAME(name,id, exist);
   IF exist=0 THEN
  /* Update query for map using mapid*/
    UPDATE MAP SET map_name=name,
               width=map_width,
               height=map_height,
               theme=map_theme,
               object_count=map_object_count
        WHERE  map_id=id
    returning map_id INTO ID ;
   ELSIF exist=1 THEN
   RAISE IsNameExist ;
   END IF;
/*Raise user defined exception, no record was effected against upadate statement */
IF SQL%NOTFOUND THEN
  RAISE UpdationFailed ;
END IF;
/*Exception block */
EXCEPTION
WHEN IsNameExist THEN
   ID:=-2; -- -2 means name already exist.
WHEN UpdationFailed THEN
 --dbms_output.put_line('Map updation failed');
  RAISE_APPLICATION_ERROR(-20002,'''Map updation failed'''||' - '||''''||sqlerrm||'''');
WHEN OTHERS THEN
 --RAISE_APPLICATION_ERROR(-20001,'Map updation failed');
 RAISE_APPLICATION_ERROR(-20002,'''Map updation failed'''||' - '||''''||sqlerrm||'''');

DataReader.RecordsAffected returning -1

我认为你应该使用

int rows = cmd.ExecuteNonQuery( );
不是

OracleDataReader dataReader = cmd.ExecuteReader( );

ExecuteNonQuery()将给出受影响的行数。

也许您需要在存储过程文本中添加"set feedback on" ?我想这和T-SQL中的"SET NOCOUNT ON"是一样的。