Mvc5 connect with Oracle 11g column with auto increament

Hi friends,

I want to make crud application in asp.net mvc connecting with oracle table having the column id (primary key) with auto increament.As oracle 11g auto increament can be done by using sequence. But how to use that sequence in asp.net model or do we have any other alternatives for this.

Your advices is highly appreciated,

Arun Sahani

I hope you know how to connect with oracle 11g in ASP.Net MVC, so I am not covering this in my answer. If not, then google for it.

I will concentrate my answer on how to use sequece in oracle 11g for auto increment column.

first create a sequence using below sample query:

CREATE SEQUENCE my_sequence
MINVALUE 1000
START WITH 1001
INCREMENT BY 1 NOCACHE NOCYCLE;

there are 2 ways now to access the value from sequence when you want to insert it in the column:

1) using it in insert query

2) using it in trigger

for 1) you can use the sequence same way as other table in your query:

select my_sequence.nextval from dual;

or if you want to use in insert query directly, then like this:

insert into mytable(auto_id, column1, column2) values(sequence_staff.nextval,col1Value, col2Value)

for 2) you can create a trigger and oracle will directly insert new value each time a new record is created in your table:

in below code auto_id is the column in which you want to insert value from sequence.

create trigger trg_mytable
before insert on mytable
for each row
begin
select my_sequence.nextval
into :new.auto_id
from dual;
end;

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Shah9y ago
Accepted answer
I hope you know how to connect with oracle 11g in ASP.Net MVC, so I am not covering this in my answer. If not, then google for it.

I will concentrate my answer on how to use sequece in oracle 11g for auto increment column.

first create a sequence using below sample query:
CREATE SEQUENCE my_sequence
MINVALUE 1000
START WITH 1001
INCREMENT BY 1 NOCACHE NOCYCLE;
there are 2 ways now to access the value from sequence when you want to insert it in the column:
1) using it in insert query
2) using it in trigger

for 1) you can use the sequence same way as other table in your query:
select my_sequence.nextval from dual;
or if you want to use in insert query directly, then like this:
insert into mytable(auto_id, column1, column2) values(sequence_staff.nextval,col1Value, col2Value)
for 2) you can create a trigger and oracle will directly insert new value each time a new record is created in your table:
in below code auto_id is the column in which you want to insert value from sequence.
create trigger trg_mytable
      before insert on mytable
      for each row
    begin
      select my_sequence.nextval
        into :new.auto_id
        from dual;
    end;
arun sahani9y ago
Thanks Nilesh ji,
Using Trigger its done !

Regards
Arun sahani