Getting the Identity after an Insert

In developing web applications I've often needed to get back a record's ID immediately after inserting it. Each database has its own method. This is the Microsoft SQL Server method I use.

INSERT INTO [TABLE](COLUMN) VALUES (@value)

SELECT CAST(scope_identity() AS INT) AS newid

Now retrieve "newid" from the database result set.

The SQL uses the safe 'scope_identity' function to get the most recent identity value for your connection. Then it casts it do an Int. If you don't do this it will come back as a Double and you have to cast it back to an Int32. Using the case we can just call GetInt32 in the Data Reader.