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||'''');
我认为你应该使用
int rows = cmd.ExecuteNonQuery( );
不是OracleDataReader dataReader = cmd.ExecuteReader( );
ExecuteNonQuery()将给出受影响的行数。
也许您需要在存储过程文本中添加"set feedback on" ?我想这和T-SQL中的"SET NOCOUNT ON"是一样的。