It had been a while since I had to grab the identity of a record after an insert.  Most of the time I’m using stored procedures to do inserts and return it as the return from the procedure — but for a variety of reasons, I needed to do this with a direct insert statement.  The process is pretty simple once you get it down.  For instance, if you have a table with an ID (identity) field, and a name field which is named Areas, you might have something like this:

cmd.CommandText = string.Format(“INSERT INTO Areas(Name) VALUES(‘{0}’); SELECT SCOPE_IDENTITY()”,                    Manager.SQLSafe(_name));
_id = Convert.ToInt32(cmd.ExecuteScalar());

In this code cmd is a SqlCommand object and Manager.SQLSafe just makes the string safe for inclusion in SQL.  Finally the SELECT SCOPE_IDENTITY() is a replacement for @@IDENTITY which doesn’t have some of the side effects that @@IDENTITY has.

You have to convert the return value to get it into an integer because it’s returned as a decimal.