CODEWITHSUNDEEP
c#oracle-databaseado.net
andrecarlucci
andrecarlucci

Reputation: 6296

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

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!!!

Upvotes: 16

Views: 41491

Answers (5)

Girish Gupta
Girish Gupta

Reputation: 1293

You can use below code.

    using (OracleCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = @"INSERT INTO my_table(name, address)
            VALUES ('Girish','Gurgaon India')
            RETURNING my_id INTO :my_id_param";
        OracleParameter outputParameter = new OracleParameter("my_id_param", OracleDbType.Decimal);
        outputParameter.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(outputParameter);        
        cmd.ExecuteNonQuery();        
        return Convert.ToDecimal(outputParameter.Value);
    }

Upvotes: 4

fahad
fahad

Reputation: 9

Select  t.userid_pk From Crm_User_Info T
Where T.Rowid = (select max(t.rowid) from crm_user_info t)

this will return your required id

Upvotes: 0

cranky974
cranky974

Reputation: 21

one possible way if one can add one column named "guid" to the table : when inserting one record from c#, generate a guid and write it to the guid column. then perform a select with the generated guid, and you have got the id of inserted record :)

Upvotes: 2

Will Marcouiller
Will Marcouiller

Reputation: 24132

Oracle uses sequences as for his identity columns, if we may say so.

If you have set a sequence for your table primary key, you also have to write a trigger that will insert the Sequence.NextValue or so into your primary key field.

Assuming that you are already familiar with this concept, simply query your sequence, then you will get your answer. What is very practiced in Oracle is to make yourself a function which will return an INT, then within your function, you perform your INSERT. Assuming that you have setup your trigger correctly, you will then be able to return the value of your sequence by querying it.

Here's an instance:

CREATE TABLE my_table (
    id_my_table INT PRIMARY KEY
    description VARCHAR2(100) NOT NULL
)

CREATE SEQUENCE my_table_seq
   MINVALUE 1
   MAXVALUE 1000
   START WITH 1
   INCREMENT BY 2
   CACHE 5;

If you want to manage the auto-increment yourself, here's how:

INSERT INTO my_table (
    id_my_table,
    description
) VALUES (my_table_seq.NEXTVAL, "Some description");
COMMIT;

On the other hand, if you wish not to care about the PRIMARY KEY increment, you may proceed with a trigger.

CREATE OR REPLACE TRIGGER my_table_insert_trg
    BEFORE INSERT ON my_table FOR EACH ROW
BEGIN
    SELECT my_table_seq.NEXTVAL INTO :NEW.id_my_table FROM DUAL;
END;

Then, when you're inserting, you simply type the INSERT statement as follows:

INSERT INTO my_table (description) VALUES ("Some other description");
COMMIT;

After an INSERT, I guess you'll want to 

SELECT my_table_seq.CURRVAL

or something like this to select the actual value of your sequence.

Here are some links to help:

http://www.orafaq.com/wiki/Sequence

http://www.orafaq.com/wiki/AutoNumber_and_Identity_columns

Hope this helps!

Upvotes: 9

Christian13467
Christian13467

Reputation: 5584

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

Upvotes: 31

Related Questions