Getting the primary key for an inserted value

Question

I have a simple table with 2 columns - name and the primary key, id. When I insert a value, I wish to be able to retrieve the value straight away in my code.

So I have :

db.Execute("INSERT INTO table(name) VALUES (@0)",name);

The id column is not automatically populated and the row is stored.

So how can I db.Query(); for this value when the name is not unique? Is this possible?

An interesting problem, I think :)

Answer 1

You can add a part to your insert that returns the identity using @@IDENTITY:

INSERT INTO table(name) VALUES (@0); SELECT @@IDENTITY;

Update: As noted in comments since @@IDENTITY works globally you should actually use SCOPE_IDENTITY() instead to limit to the current scope:

INSERT INTO table(name) VALUES (@0); SELECT SCOPE_IDENTITY();

Then you can retrieve the primary key / identity by executing your insert with ExecuteScalar() and grabbing the result.