Retrieve AutoIncrement key value when the column is NOT the first one in the table

Question

Hello there,

I've got a question regarding how to retrieve the auto-increment or identity value for a column in SQL Server 2005, when said column is not the first declared column in a table.

I can get the generated value for a table just by issuing the following code:

MyTable newRecord = new MyTable();
newRecord.SomeColumn = 2;
newRecord.Save();
return newRecord.MyIdColumn;

Which works fine regardles of how many other columns make up the primary key of that particular table, but the first column declared MUST be the identity column, otherwise this doesn't work.

My problem is that I have to integrate my code with other tables that are out of my reach, and they have identity columns which are NOT the first columns in those tables, so I was wondering if there is a proper workaround to my problem, or if I'm stuck using something along the lines of SELECT @@IDENTITY to manually get that value?

Many thanks in advance for all your help!

Answer 1

From the "ewww gross" department:

Here's my workaround for now, hopefully someone may propose a better solution to what I did.

MyTable newRecord = new MyTable();
newRecord.SomeColumn = 2;
newRecord.Save();
CodingHorror horror = new CodingHorror();
string SQL = "SELECT IDENT_CURRENT(@tableName)";
int newId = horror.ExecuteScalar<int>(SQL, "MyTable");
newRecord.MyIdColumn = newId;
newRecord.MarkClean();
return newRecord.MyIdColumn;