How do I get the last id entered with Dapper?

Question

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;
        }
    }

`SCOPE_IDENTITY()` was used in T-SQL before 2005 and the OUTPUT clause. It doesn't work in Oracle and shouldn't be used in T-SQL either — Panagiotis Kanavos, Mar 19 '19 at 11:20
As this [possibly duplicate question shows](https://stackoverflow.com/questions/34811283/retrieve-oracle-last-inserted-identity) you need to use .CURRVAL with the sequence that provides values for the key, eg `SELECT USER_SEQUENCE.CURRVAL FROM dual;` — Panagiotis Kanavos, Mar 19 '19 at 11:22
Could you let me see a draft of how the query should be written? Because this thing is not clear to me. Thank you — Elmoro, Mar 19 '19 at 11:26
The duplicate shows exactly that. The query should be `SELECT USER_SEQUENCE.CURRVAL FROM dual;`. Don't forget the semicolon either — Panagiotis Kanavos, Mar 19 '19 at 11:28
I wrote this, but it throws me the same exception as before 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 USER_SEQUENCE.CURRVAL FROM dual;"; — Elmoro, Mar 19 '19 at 11:32

Gabriel Lima · Answer 1 · 2019-03-19T09:48:41.687

0

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();");

edited Mar 19 '19 at 09:48

answered Mar 19 '19 at 09:42

Gabriel Lima

348
3
11

Nothing to do, it gives me the same exception. Thank you – Elmoro Mar 19 '19 at 09:53
@Elmoro actually your sql params must be written like `@param` instead of `:param. And the properties of the object used to hold their values are case sensitive. – Gabriel Lima Mar 19 '19 at 09:58
I have done other query inserts and the parameters it takes me as :param and not @param – Elmoro Mar 19 '19 at 10:01

score -2 · Answer 2 · edited Mar 19 '19 at 11:16

-2

insert into your_table (...)
output INSERTED.(column name) 
values (...)

edited Mar 19 '19 at 11:16

AS Mackay

2,831
9
19
25

answered Mar 19 '19 at 09:31

user3535420

1

This is an Oracle question. The OUTPUT clause works only on SQL Server. Not my downvote by the way, but this answer doesn't apply – Panagiotis Kanavos Mar 19 '19 at 11:23

score -3 · Accepted Answer · answered Mar 20 '19 at 09:06

-3

Post SOLVED! I used the transactions and everything worked! ;)

answered Mar 20 '19 at 09:06

Elmoro

98
1
13

1

It is *really* odd that the presence of a transaction should somehow fix "command terminated incorrectly". Are you **sure** that's all you did? – Lasse V. Karlsen Mar 20 '19 at 09:11

How do I get the last id entered with Dapper?

3 Answers3