It is easy to implement the automatic growth of inserting data in SQL Sever, but it is not easy to do this in an Oracle database, And you want to be in. NET to get the ID of the newly inserted data, and not to read the error, it is even more difficult, in order to resolve the data inserted in the Oracle data can be ID, get the ID of the new data, and avoid the problem of the ID read error caused by concurrent operation.
Data table structure is test (id,name)
First, solve the data ID self-increase problem
Create a sequence sequence (sequence details can be searched from the Internet, here do not repeat)
Create sequence Seq_test
MinValue 1
MaxValue 99999999999999999999999999
Start with 21
Increment by 1
Cache 20;
Sequence sequence has two intrinsic properties Nextval (next value) and Currval (current value)
This will automatically get the self-added ID value in the SQL statement that inserts the data: INSERT INTO Test (id,name) VALUES (seq_test.nextval, ' name1 ')
After the insertion is complete, you can get the ID value: select Seq_test.currval from dual
Note: The Currval property of the sequence only forms a short record after the INSERT statement has been inserted, so the value must be fetched before the next database operation, otherwise the invalidation cannot be obtained, and in order to solve this problem, we can get the value in time by creating a stored procedure.
Then, you create a stored procedure that implements the insert and value actions
In order to achieve scalability of the stored procedure, the parameters of the stored procedure include three: Strinsertsql (passing the INSERT statement), Seqname (get the name of the sequence), ID (output value, get ID)
Copy Code code as follows:
Create or replace procedure P_getitemid (
Strinsertsql VARCHAR2,
Seqname VARCHAR2,
ID out VARCHAR2
)
Is
strSQL varchar (200);
Begin
Execute immediate strinsertsql;--Execute INSERT statement
strsql:= ' SELECT ' | | seqname| | '. Currval from dual ';--SQL statement to get ID
Execute immediate strsql into id;--SQL statement to get ID
EXCEPTION
When others THEN
Dbms_output. Put_Line (SQLERRM);
End P_getitemid;
Finally, use the C # language to implement program application in. Net
Also, to implement the extended row for this feature, create a function that gets the ID, and the database connection statement gets from the Webconfig
Copy Code code as follows:
public string Executeprocedure (string strinsertsql,string proname,string seqname)
{
Try
{
if (M_CONNECTION_ORC. State!= System.Data.ConnectionState.Open)
{//Get database connection from Webconfig
M_connection_orc. Open ();
}
}
catch (System.Exception ex)
{
}
Try
{//Blue font will be the core statement
OracleConnection Dbconnection_orc;
OracleCommand Dbcommand_orc;
Dbconnection_orc = M_CONNECTION_ORC;
Dbcommand_orc = new OracleCommand (Proname, DBCONNECTION_ORC); Proname is the name of the stored procedure
Dbcommand_orc.commandtype = CommandType.StoredProcedure;
The parameter names added in parameters must be consistent with the name and type of the parameters in the stored procedure in 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; Value that specifies that the parameter ID is an output type
Dbcommand_orc. parameters["Strinsertsql"]. Value = Strinsertsql; Get INSERT statement
Dbcommand_orc. parameters["Seqname"]. Value = Seqname; Get sequence Name
Dbcommand_orc. ExecuteNonQuery (); Perform an action
String NewID = Dbcommand_orc. parameters["ID"]. Value.tostring (); Get the value of the ID
return NewID; Return ID value
}
catch (System.Exception ex)
{
Throw ex;
}
Finally
{
CloseConnection ();
}
}
At this point, the ID value operation to get the newly inserted data is over, and in the programming process, the call function executeprocedure can get the ID value, wow, click
