Can anyone tell me how to get the identity of the last inserted row? I'm using DLINQ so please don't tell me how to do this in SQL as I know this. In SQL I would use SCOPE_IDENTITY() or @@IDENTITY (generally not the latter)

Please help! I have project to be finished ASAP and this is the only bit holding me back!

Thanks !

 

Please help! I will resolve to begging!!!

Redtap, is this a record whose primary key is auto-id'd on insert?

Yes, its an identity column, when a record is inserted the identity is auto generated by the db, this is the value i wish to return after the insert,

many thanks!

Sorry, there is no non-SQL way to get the last auto-generated identity in the db. Typically, Blinq/DLinq can handle multiple inserts in a concurrent environment and hence within the scope of a DataContext, the last id is really not applicable. Hence we have not added an API. If you are inserting a single object, then the id of that object is patched up using @@identity/scope_identity. So if you are looking for the id of the inserted object, that is available. If you are looking for the last, database-wide id, you will need to use the SQL command (directly or via DataContext pass through - ExecuteQuery() ).

I am curious to understand your scenario for our future API design.

Thanks.

Dinesh
(Program Manager - DLinq)

Thanks Dinesh and Polita for getting back to and helping me out. I'm still finding things a little unclear in regards to this line. I quote

" If you are inserting a single object, then the id of that object is patched up using @@identity/scope_identity. So if you are looking for the id of the inserted object, that is available."

Here is a simplified idea for what I'm trying to achieve...

I have an object called 'Nomination' each 'Nomination' object can have a number of file objects attached to it, so it has a collection of 'File' objects.

Nomination object has NominatedBy, NominationId, and a collection of File objects.

File object has Filename, and NominationId.

The relationship is one to many.

Nomination n = new Nomination();

n.NominatedBy = "Paul Wroe";

Nomination.Insert(n);

File f = new File();

f.NominationId = // here is where I need to tell the file object the Id of the just inserted Nomination

Hope this explains things a little clearer and you can suggest a way to achieve this? or point me towards some documentation.

Kind regards

Paul Wroe

 

 

Sorry to keep barking on about this, but I have a project I need to finish and really need to crack this problem!

Many thanks

Ok, Ok Its finally clicked for me...being an old sql developer getting my head round things like this gets confusing so to take this confusion away I've put together an example below which will hopefully help out others who were also confused...I hope I wasn't the only one...

using TestDb;

namespace

LINQConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

// Insert example **********************

// create the database connection

Test db = new Test("Data Source=********;Initial Catalog=Test;User ID=PaulWroe; password=******");

// Create a new class (as in class of pupils at a school, Maths, English etc)

Class englishClass = new Class();

// set the name of the class

englishClass.ClassName =

"English";

// create a child

Child student = new Child();

// set the name of the child

student.ChildName =

"James Bankcroft";

// add the child to the english class we just made, should be children

// not childs for readability, but this can be changed manually after pluralise has been used

englishClass.Childs.Add(student);

// add the english class to the database (with the child (James) in the class)

db.Classes.Add(englishClass);

// commit changes to the database and class is added to class table,

// child is added to the child table and the id's and relationship is

// maintained hence no need to use SCOPE_IDENTITY or @@IDENTITY manually!

db.SubmitChanges();

// Update example **********************

// Create a new class for maths

Class mathsClass = new Class();

// change the classname property

mathsClass.ClassName =

"Maths";

// get james back from the childs table

var some_child =

(

from c in db.Childs

where c.ChildName == "James Bankcroft"

select c

).Single<

Child>();

// and move james from the english class to the newly created maths class

mathsClass.Childs.Add(some_child);

// commit changes

db.SubmitChanges();

}

}

}

Based on a quick look, this seems about right. DLinq should take care of flowing the autogen id to related objects if they are mapped correctly (Blinq will do this if you have a foreign key relationship).

Thanks.
Dinesh

Are you interested in another approach?  I use SQL Server's "inserted" and "deleted" temporary tables within a trigger to get the identity of the row just inserted/deleted.  If interested, I'll further explain this technique.  BTW, I use Visio to design and create my databases.  Within that design context, Visio creates triggers used for maintaining integrity on updates, inserts, and deletes.

A Simple Way to get the ID. With Example

dim authorid as string = "" 

Sql = "Insert into author(name,title) values('yahya','ASP.NET') 

Sqlcommand.comandtext = sql
SqlCOmmand.Connection = SqlCOnnection  ' Define your  sql connection
Sqlcommand.connection.open()
SqlCommand.ExecuteNonQuery()

SqlCommand.Commandtext = "Select @@IDENTITY as authorid"   ' Your authorid should be an identity column .
Dim dreader as Data.SqlCLient.SqlDatareader

dreader = SqlCOmman.ExecuteReader(CommandBehaviour.closeconnection)
if dreader.HasRows then
 dreader.Read()
authorid = dreader.item("authorid")
End if

Now You can return the autorid to any methods or Function as you like. Hope this will solve your problem.

 

do u mean to get the last inserted value if the  identity using DLINQ ?

 then it will the value once you execute the submitchange method