I'm querying DB Oracle and would like to return the entered id. I use a sequence that automatically generates the next id for me. I've already tried this, but I have an "ORA-00933 sql command terminated incorrectly" exception. What is the error? Thank you
public async Task<int> InsertSESSIONUSER_TAsync(SESSIONUSER_T obj)
{
string sql = "INSERT INTO SESSIONUSER_T (ID, USERNAME,PASSWORD,LOCALE,TIMEZONEID,EMAIL,CREATIONDATE, EMAILPEO) VALUES (USER_SEQUENCE.NEXTVAL,'TEMP',:PASSWORD,:LOCALE,:TIMEZONEID,:EMAIL,:CREATIONDATE,:EMAILPEO); SELECT CAST(SCOPE_IDENTITY() as int)";
using (OracleConnection cnn = DBCConnectionFactory.Getconnection())
{
try
{
cnn.Open();
int row = await cnn.ExecuteAsync(sql, obj);
var result = await cnn.QueryAsync<int>(sql, obj);
return result.Single();
}
catch (Exception ex)
{
ApplicationLogger.Logger.Error(ex, "InsertSESSIONUSER_TAsync");
}
finally
{
if (cnn?.State == System.Data.ConnectionState.Open)
{
cnn.Close();
}
}
return -1;
}
}
Your SQL must have 2 clauses. The insert and the select last inserted id. This isn't DBMS agnostic, so for instance, in MySQL, it would be something like:
INSERT INTO <table> (...) VALUES (...); SELECT last_insert_id();
And on the application side, you'd have to use Dapper's extension method ExecuteScalarAsync
. For instance:
// ...
var lastId = await conn.ExecuteScalarAsync<long>("INSERT INTO <table> (...) VALUES (...); SELECT last_insert_id();");