. Net to obtain the id of the newly inserted data in the oracle database

It is easy to achieve automatic growth of inserted data in SQL Server, but it is not easy to implement this operation in oracle Database. net to obtain the id of the newly inserted data, and there will be no read errors, it is more difficult, in order to solve the problem of auto-increment id of the data inserted in oracle data, obtain the id of the new data at the same time, and avoid id read errors due to concurrent operations.

The data table structure is test (id, name)

First, the data id auto-increment problem is solved.
Create a sequence (you can search for sequence details on the Internet. I will not go into details here)
Create sequence SEQ_test
Minvalue 1
Max value 99999999999999999999999999
Start with 21
Increment by 1
Cache 20;
Sequence has two inherent attributes: nextval (next value) and currval (current value)
In this way, the auto-increment id value can be automatically obtained in the SQL statement of inserting data: insert into test (id, name) values (seq_test.nextval, 'name1 ')
After insertion, you can obtain the id value: select seq_test.currval from dual
Note: The currval attribute of a sequence only forms a short record after the insert statement is completed. Therefore, you must obtain the value before the next database operation. Otherwise, the value cannot be obtained after the insert statement is executed. To solve this problem, you can obtain this value in time by creating a stored procedure.
Then, create a stored procedure to insert and obtain values.

To achieve the scalability of the stored procedure, three parameters are included: strInsertSQL (insert statement passed), seqName (get sequence name), ID (output value, get id)Copy codeThe Code is as follows: create or replace procedure p_GetItemID (
StrInsertSQL varchar2,
SeqName varchar2,
ID out varchar2
)
Is
StrSql varchar (200 );
Begin
Execute immediate strInsertSQL; -- execute the insert statement
StrSql: = 'select' | seqName | '. currval from dual'; -- Obtain the id of an SQL statement
Execute immediate strSql into ID; -- execute the SQL statement to obtain the id
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE (SQLERRM );
End p_GetItemID;

Finally, the c # language is used to implement program applications in. net.

Similarly, in order to implement this function, create a function to obtain the id, and obtain the database connection statement from webconfig.Copy codeThe Code is as follows: public string ExecuteProcedure (string strInsertSQL, string proName, string seqName)
{
Try
{
If (m_Connection_orc.State! = System. Data. ConnectionState. Open)
{// Obtain the database connection from webconfig
M_Connection_orc.Open ();
}
}
Catch (System. Exception ex)
{
}
Try
{// The core statement of the blue font
OracleConnection dbConnection_orc;
OracleCommand dbCommand_orc;
DbConnection_orc = m_Connection_orc;
DbCommand_orc = new OracleCommand (proName, dbConnection_orc); // proName indicates the name of the stored procedure.
DbCommand_orc.CommandType = CommandType. StoredProcedure;
// The parameter name added to Parameters must be consistent with the parameter name and type in the stored procedure of the database.
DbCommand_orc.Parameters.Add ("strInsertSQL", OracleType. VarChar );
DbCommand_orc.Parameters.Add ("seqName", OracleType. VarChar );
DbCommand_orc.Parameters.Add ("ID", OracleType. VarChar );
DbCommand_orc.Parameters ["ID"]. Direction = ParameterDirection. Output; // specify the parameter ID as the value of the Output type.
DbCommand_orc.Parameters ["strInsertSQL"]. Value = strInsertSQL; // obtain the insert statement
DbCommand_orc.Parameters ["seqName"]. Value = seqName; // obtain the sequence name.
DbCommand_orc.ExecuteNonQuery (); // execute the operation
String newID = dbCommand_orc.Parameters ["ID"]. Value. ToString (); // obtain the id Value
Return newID; // return the id value.
}
Catch (System. Exception ex)
{
Throw ex;
}
Finally
{
CloseConnection ();
}
}

So far, the operation to obtain the id value of the newly inserted data is complete. During the programming process, you can obtain the id value by calling the ExecuteProcedure function.