我试图在Oracle数据库表中执行插入操作,并返回新创建的主键(通过触发器和序列)。
我有一段有效的代码。我有第二段代码,它在ExecuteNonQuery()上返回错误。
我一生无法确定第一个代码为何有效而第二个代码无效。
工作的第一个代码:
string sqlStr = @"INSERT INTO LEI_EROUTER_SESSIONS(empCID, JOBNUMBER, EMP_NAME, LOGGEDON, MACHINENAME, IP) values(:empCID, :JOBNUMBER, :EMP_NAME, sysdate, :MACHINENAME, :IP) RETURNING C_ID INTO :LASTCID";
int C_ID;
using (RetryClass RetryClassInstance = new RetryClass(CallingForm, JobSessionData, UserData))
{
do
{
RetryClassInstance.Retry = false;
C_ID = 0;
OracleConnection conn = new OracleConnection(Machine_Data.oracle_connstr);
OracleCommand cmd = new OracleCommand(sqlStr, conn);
try
{
conn.Open();
cmd.Parameters.Add("empCID", UserData.employee_cid);
cmd.Parameters.Add("JOBNUMBER", JobSessionData.jobnumber);
cmd.Parameters.Add("EMP_NAME", UserData.employee_name);
//cmd.Parameters.Add("LOGGEDON", DateTime.Now);
cmd.Parameters.Add("MACHINENAME", Environment.MachineName);
cmd.Parameters.Add("IP", GlobalFunctions.LocalIPAddress());
cmd.Parameters.Add("LASTCID", OracleDbType.Int32, ParameterDirection.Output);
cmd.ExecuteNonQuery();
C_ID = Convert.ToInt32(cmd.Parameters["LASTCID"].Value.ToString());
}
catch (Exception ex)
{
RetryClassInstance.HadException(ex);
}
finally
{
if (cmd != null) cmd.Dispose();
if (conn != null) conn.Dispose();
}
} while (RetryClassInstance.Retry == true);
}
return C_ID;
这是不起作用的第二段代码:
string C_ID;
List<string> C_IDS = new List<string>();
string sqlStr = @"INSERT INTO LEI_CHECKIN_QUEUE(CHECKIN_DATE, JOB_ID, CELL, SN, STEP_NAME, STEP_TYPE, START_SEQ,
END_SEQ, CHECKEDINBY_EMP_CID, IN_QUEUE, PRIORITY_CODE, STEP_STARTED_WHEN, GROUP_CID)
VALUES(SYSDATE, :JOB_ID, :CELL, :SN, :STEP_NAME, :STEP_TYPE, :START_SEQ, :END_SEQ, :CHECKEDINBY_EMP_CID, 'Y', null,
null, null) RETURNING C_ID INTO :LASTCID";
using (RetryClass RetryClassInstance = new RetryClass(this, JobSessionData, UserData))
{
do
{
RetryClassInstance.Retry = false;
OracleConnection conn = new OracleConnection(Machine_Data.oracle_connstr);
OracleCommand cmd = new OracleCommand(sqlStr, conn);
try
{
conn.Open();
foreach (string SN in JobSessionData.serial_numbers)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("JOB_ID", JobSessionData.jobnumber);
cmd.Parameters.Add("CELL", JobSessionData.cell);
cmd.Parameters.Add("STEP_NAME", StepName);
cmd.Parameters.Add("STEP_TYPE", StepType);
cmd.Parameters.Add("START_SEQ", Start_Seq);
cmd.Parameters.Add("END_SEQ", End_Seq);
cmd.Parameters.Add("CHECKEDINBY_EMP_CID", UserData.employee_cid);
cmd.Parameters.Add("SN", SN);
cmd.Parameters.Add("LASTCID", OracleDbType.Int64, ParameterDirection.Output);
cmd.ExecuteNonQuery();
C_ID = cmd.Parameters["LASTCID"].Value.ToString();
C_IDS.Add(C_ID);
}
}
catch (Exception ex)
{
RetryClassInstance.HadException(ex);
}
finally
{
if (cmd != null) cmd.Dispose();
if (conn != null) conn.Dispose();
}
} while (RetryClassInstance.Retry == true);
}
从第二段代码返回的错误如下:
System.IndexOutOfRangeException: Index was outside the bounds of the array.
at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int32 initialLOBFetchSize, Int64[] scnFromExecution, Boolean& bAllPureInputBinds, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int32 lobPrefetchSize, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
两者都在同一个Oracle数据库实例上运行。如果我注释掉与OUTPUT PARAMETER相关的行并从SQL INSERT语句中删除return子句,则第二段代码可以正常工作。
同样,LEI_CHECKIN_QUEUE表的确有一个名为C_ID的列,它是主键。
是否有人对第二段代码为什么不起作用和/或我可以采取哪些措施加以解决?
谢谢!
最佳答案
您需要声明变量,如下所示。
根据经验,在将查询嵌入代码之前,请始终在Oracle服务器上对其进行测试。最重要的是,最重要的是使用参数化的存储过程来避免sql注入攻击。因此,请勿将查询嵌入到您的代码中。
@"declare LASTCID number;
INSERT INTO LEI_CHECKIN_QUEUE(CHECKIN_DATE, JOB_ID, CELL, SN, STEP_NAME, STEP_TYPE, START_SEQ,
END_SEQ, CHECKEDINBY_EMP_CID, IN_QUEUE, PRIORITY_CODE, STEP_STARTED_WHEN, GROUP_CID)
VALUES(SYSDATE, :JOB_ID, :CELL, :SN, :STEP_NAME, :STEP_TYPE, :START_SEQ, :END_SEQ, :CHECKEDINBY_EMP_CID, 'Y', null,
null, null) RETURNING C_ID INTO :LASTCID";