How to get value of auto generated primary key after insert

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
genesplitter
Posts: 10
Joined: Sat 08 Aug 2009 00:08

How to get value of auto generated primary key after insert

Post by genesplitter » Fri 18 Feb 2011 20:01

I am trying to insert an new user into my user table. The userID column is a primary key generated from Oracle sequence. I set Auto Generated Value = true but I noticed that the userID always ends up as 0. Of course inserting a user via a sql insert does properly generate a new sequence value.

Later I also tried combinations of Auto-Sync, Read-Only, and even set IdGenerator to the name of the oracle sequence name. I didn't expect changing these properties from their defaults would work either but I couldn't find an online example so I wanted to try it out to make sure. I'm also using the latest dotConnect for Oracle 6.1

I'm sure I'm overlooking something obvious. Any ideas (of even better a link to the documentation covering this so I can teach myself).

On a related note, I need the userID value returned so I can use it in my many to many UserRoles table. Do I have to call SubmitChanges() to insert the user, get the userID, and then create the UserRoles record and call SubmitChanges() a 2nd time?

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 22 Feb 2011 10:54

For example, you can perform the following:
- set the 'Auto Generated Value' property of the corresponding field to true;
- set its IdGenerator property to 'Sequence';
- at this moment, the Sequence property should become available; set it to the name of your Oracle sequence;
- re-generate the code for the model.

In case you already have a trigger that inserts the next sequence value into the table id, you can just set the 'Auto Generated Value' property to true and the IdGenerator property to the default 'None' value.

For more information about possible ways of Id generation, please refer to
http://www.devart.com/linqconnect/docs/ ... ation.html

As for your question about the related entity, you can insert both entities in a single SubmitChanges() call, like

Code: Select all

User newUser = new User() { ... };
dataContext.Users.InsertOnSubmit(newUser);

UserRole newUserRole = new UserRole() { User = newUser, Role = ..., ... };
dataContext.UserRoles.InsertOnSubmit(newUserRole);

dataContext.SubmitChanges();
Please tell us if this helps.

edvaldig
Posts: 5
Joined: Mon 14 Dec 2009 15:21

Post by edvaldig » Wed 13 Apr 2011 10:09

Confirmed, setting the AutoGeneratedValue property in the entity editor solved my issue which was nearly identical

jlentrichia
Posts: 12
Joined: Wed 11 May 2011 15:12

Not Loading Sequences from XML

Post by jlentrichia » Fri 20 May 2011 22:30

Set IdGenerator to Sequence and specify a sequence name.

Save the document.

Close.

Open.

Sequence is no longer set.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 23 May 2011 13:00

We've answered you in this topic:
http://www.devart.com/forums/viewtopic.php?t=21073

Post Reply