How to get value of auto generated primary key after insert

How to get value of auto generated primary key after insert

Postby 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?
genesplitter
 
Posts: 7
Joined: Sat 08 Aug 2009 00:08

Postby 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/IDGeneration.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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby edvaldig » Wed 13 Apr 2011 10:09

Confirmed, setting the AutoGeneratedValue property in the entity editor solved my issue which was nearly identical
edvaldig
 
Posts: 5
Joined: Mon 14 Dec 2009 15:21

Not Loading Sequences from XML

Postby 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.
jlentrichia
 
Posts: 12
Joined: Wed 11 May 2011 15:12

Postby StanislavK » Mon 23 May 2011 13:00

We've answered you in this topic:
http://www.devart.com/forums/viewtopic.php?t=21073
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle