Get auto_increment ID from SQL Compact Edition

Question

I am prototyping an app using ASP.NET MVC and SQL Compact Edition on VS2008. I have 2 entities that have have a many-to-many relationship with each other. When I save objects to the database through hand-crafted SQL I need to find out what their ID is so I can drop a record into the mapping table between the two entities. The auto_increment ID field is in fact the only unique identifier for the tables. So after I've done my insert, how do I re-load the record and grab the newly assigned ID?

RE: Code-is-art, I am using the builtin SqlCeConnection, SqlCeCommand, and SqlCeDataReader objects from the .NET 3.5 library.

Answer 1

Use "SELECT @@IDENTITY" in a subsequent call to the database.

Compact is single user access you don't need to worry about transactions etc to protect against other inserts.