How to get the generated id from an inserted row using ExecuteScalar?

Question

I know that in Oracle I can get the generated id (or any other column) from an inserted row as an output parameter. Ex:

insert into foo values('foo','bar') returning id into :myOutputParameter

Is there a way to do the same, but using ExecuteScalar instead of ExecuteNonQuery?

I don't want to use output parameters or stored procedures.

ps: I'm using Oracle, not sql server!!!

Answer 1

If you are on oracle, you have to use ExecuteNonQuery and ResultParameter. There is no way to write this as query.

using (OracleCommand cmd = con.CreateCommand()) {
    cmd.CommandText = "insert into foo values('foo','bar') returning id into :myOutputParameter";
    cmd.Parameters.Add(new OracleParameter("myOutputParameter", OracleDbType.Decimal), ParameterDirection.ReturnValue);
    cmd.ExecuteNonQuery(); // an INSERT is always a Non Query
    return Convert.ToDecimal(cmd.Parameters["myOutputParameter"].Value);
}