For operations performed on the database when IDs are obtained during various database Insert operations, IDs are obtained at the same time as Insert operations to ensure atomic operation. Instead, take the Id first, and then insert it to the database.
Oracle:
Use the Returning into statement that comes with Oracle. the code example is as follows:
1 sql = @"INSERT INTO KEYWORD(KEYWORD_ID,SITE_ID,KEYWORD_NAME,STATUS,SOURCE,CREATE_TIME,LAST_CHANGED) 2 VALUES 3 ( 4 KEYWORD_SEQ.nextVal, 5 :SITE_ID, 6 :NAME, 7 1, 8 :Source, 9 SYSDATE,10 SYSDATE+0.000711 ) RETURNING KEYWORD_ID into :Key_ID";12 13 com.CommandText = sql;14 //com.Parameters.Add(new OracleParameter(":ID", OracleDbType.Int64, allKeyWords.Select(n => n.Keyword_Id).ToArray(), ParameterDirection.Input));15 com.Parameters.Add(new OracleParameter(":SITE_ID", OracleDbType.Int64, allKeyWords.Select(n => n.Site_Id).ToArray(), ParameterDirection.Input));16 com.Parameters.Add(new OracleParameter(":NAME", OracleDbType.NVarchar2, allKeyWords.Select(n => n.Keyword_Name).ToArray(), ParameterDirection.Input));17 com.Parameters.Add(new OracleParameter(":Source", OracleDbType.NVarchar2, allKeyWords.Select(n => n.Source).ToArray(), ParameterDirection.Input));18 var outputIdParm = new OracleParameter(":Key_ID", OracleDbType.Int32, ParameterDirection.Output);19 com.Parameters.Add(outputIdParm);20 21 com.ExecuteNonQuery();22 23 var allKeyWordIds = outputIdParm.Value as OracleDecimal[];
Note the Returning statement in the above code and the outPut of the final parameter.
SqlServer: use the @ IDENTITY keyword that comes with SqlServer. the code example is as follows:
1 var cmd = dbMenloFramework.CreateCommand("insert into syslog (Thread) values('test') SELECT @@IDENTITY AS Id");2 using(var read = dbMenloFramework.ExecuteDataReader(cmd)){3 read.Read();4 Console.WriteLine(read[0].ToString());5 }
MySql:
It is temporarily missing. if some netizens have reliable methods, please contact me in the comments.