I have a small web application, which inserts data into a database. A column in one of the table is an auto incremented id. Once I insert a row, I need to get the id just inserted to continue with other tables. So far so good, nothing complex and it is something I have done it before. In mysql there is a function LAST_INSERT_ID() get this. I always use database specific function to do this to avoid problems and better thread safety.
But the other day my client instructed me to keep the code database independent. Because in few months time he is planning to change his hosting server, and go in for MSSQL or Oracle for his other applications, so this app will be moved too.
So, I have to move the id retrieval logic to application instead of the database.
What is the best database neutral way to get the last inserted auto incremented Id in a thread safe manner in ASP.NET (1.1 or 2.0)?
My database inserts in general will be as below: table1 contains the column which is autogenerated(columnname : ID)
insert into table1 (Password) values ('password');
-> I have to get my last inserted id here
insert into table2 (ID, email, tel) values ('id','email','tel')