How to get last created record identifier from Sql database using Asp.Net

Question

I will explain the problem with an example:

There are two tables in my database: named, tags

Both tables have a column named ID_ENTRY. When I add a record to a table, a record, I have to take ID_ENTRY from the last record added and add it to the table, tags. How can i do this?

Answer 1

Immediately after executing the insert statement in the first table, you should query @@ IDENTITY by doing "SELECT @@ identity". This will extract the last autogenerated identifier ... and then just paste it into the second table.

If you use triggers or something that inserts rows ... this may not work. Use Scope_Identity () instead of @@ IDENTITY

Answer 2

The only way to do this is with multiple statements. Using dynamic sql, you can do this by separating each statement in the query string with a colon:

"DECLARE @ID int;INSERT INTO [Entry] (...) VALUES ...; SELECT @ID = scope_identity();INSERT INTO [TAGS] (ID_ENTRY) VALUES (@ID);" 

Make sure you put this in a transaction to protect against concurrency problems and keep all atomic. You can also split this into two separate queries to return a new ID value in the middle if you want; just make sure both requests are in the same transaction.

Also: you use parameterized queries with your dynamic sql, right? If you do not, I personally will come there and strangle you 10,000 times with wet noodles until you repent of your insecure ways.

Answer 3

I would probably do this using the INSERT trigger in the named input table if you have all the data you need to click in the tag table. If not, then you might want to use a stored procedure that creates both inside a transaction.

If you want to do this in code, you need to be more specific about how you manage your data. Are you using DataAdapter, DataTables, LINQ, NHibernate, ...? In fact, you need to wrap both inserts inside a transaction of a certain type, so that either inserts are executed or not, but the means for this depend on what technology you use to interact with the database.

Answer 4

If you use dynamic sql, why not use Linq for the Entity Framework, now EF recommends Microsoft data access technology (see this post Explaining the message on L2S Futures from the ADO.NET team blog), and if you insert EF, then the last identifier the identifier will be available to you automatically, I use it all the time.

Hope this helps!

Ray