How can I retrieve the last identity value inserted with Dapper?

asp.net-mvc c# dapper oracle

Question

I want to get the inputted id back from the Oracle database when I query it. I use a sequence that provides me with the next ID automatically. The "ORA-00933 sql command finished erroneously" error prevents me from doing this, as I have previously done. What's wrong, exactly? Many thanks

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;
        }
    }
1
0
3/19/2019 9:18:19 AM

Accepted Answer

Submit SOLVED! Everything was functional when I utilized the transactions;)

-3
3/20/2019 9:06:32 AM

Popular Answer

You need two clauses in your SQL. the last entered ID, both in insert and select. This isn't DBMS-neutral, thus for example, in MySQL, it may look like:

INSERT INTO <table> (...) VALUES (...); SELECT last_insert_id();

Additionally, you'd need to utilize Dapper's extension mechanism on the application side.ExecuteScalarAsync . For illustration:

// ...
var lastId = await conn.ExecuteScalarAsync<long>("INSERT INTO <table> (...) VALUES (...); SELECT last_insert_id();");


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow