Avatar of maXXXeE
maXXXeE
 asked on

database independent last inserted row

Hi

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.

Question:
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)?

Note:
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')
ASP.NET

Avatar of undefined
Last Comment
GaryFrancisLond

8/22/2022 - Mon
Ashutosh Vyas

insert into table2 (ID, email, tel) values ('id','email','tel')

will this not help?

select max(id) from table2
SOLUTION
Vivek Thangaswamy

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
maXXXeE

ASKER
ashutosh9910 :
does max(id) guarantee than it is the id that is inserted by particular instance?  It just gives me the last id inserted by some instance.
maXXXeE

ASKER
vivekthangaswamy:
I am trying to perform this in application layer itself.
Will consider stored procedure if no other options exist.

Thanks anyway.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ashutosh Vyas

since id is the identity column as per your remark

Note:
My database inserts in general will be as below: table1 contains the column which is autogenerated(columnname : ID)

the max of id would always be the last inserted record.
GaryFrancisLond

Select max(id) woudl nto work because if you insert 10 records for example that have generated ID's 1 to 10 and then you delete the last 3 records, select max(id) will return 7 but the next identity could possibly be 11.

Creating a stored procedure will also not solve yur issue. Although the code that vivekthangaswamy has postd looks like it will work, this is not database independent it is Microsoft SQL specific.

I cannot think of a way that this can be done whilst remaining database independent without using a Data Access Layer that accesses an underlying class based on the database you are connecting to.This would basically jsut be hiding the database independence though as essentially you will still be calling database independent code to retrieve the identity value.

If anyone can come up with a better solution, i would be interessted to knwo though.

Regards,

Gary Francis
Ashutosh Vyas

if you insert 10 records for example that have generated ID's 1 to 10 and then you delete the last 3 records, select max(id) will return 7 but the next identity could possibly be 11.

Until you insert Id 11 you have 7 as last row and max(id) would return 7

once you insert a new record id = 11 and max id would return 11 so whats wrong with that??

Could  u plz explain.. I guess this is what he wants.............
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GaryFrancisLond

My apologies ashutosh9910. I wasn't reading the question/answers properly - that would work.

I have only really worked with access, sql and mysql - do all other databses (e.g. oracle) support the max(fieldname) command?

If they do, I would suggest that is the easiest way to get what is required.

Regards,

gAry Francis
Ashutosh Vyas

yup works with ORacle as well.............
maXXXeE

ASKER
ashutosh9910:
Please clear my doubts regarding your solution as I am not an expert in sql.
As per your solution this should be my solution
SQL Line 1 :  insert into table1 (Password) values ('password');
--> asp.net code
SQL Line 2 :  select max(id) from table1;

Say the id inserted by one session is 20.
But between SQL Line, there might be few ASP.net code, which takes negligible time, but within that time, say few other sessions insert 5 more rows.
What will max(id) return. 20 or 25 ???
Your help has saved me hundreds of hours of internet surfing.
fblack61
Vivek Thangaswamy

maXXXeE:
You asked a doupt to ashutosh9910 in previous post, in that situation the max ID concept fails. My solution will work fine on that situation, but i have given sample code for MS SQL server 2000, for more you can try for Oracle also.
Ashutosh Vyas

even in that case max id should work fine and would be easier to implement by just making a small change to the table. Make another column containing UserId or something like AddedBy and filter the same accordingly.

Select max(id) from table2 where AddedBy = currentuser
maXXXeE

ASKER
thanx to all who have replied.
But if you read the question, i have mentioned "So, I have to move the id retrieval logic to application instead of the database".
Everyone provided me database solution.

Is it possible to use the transactions in asp.net to get over this problem?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GaryFrancisLond

ASP.Net does not use database independet code. Each type of database has a set of data access components that are all very similar but are specific to each data type e.g. SqlConnection will only work with sql, MysqlConnection will work with mysql OleConnection will work ole database/files etc.

For this reason there is no "one" command to find the identity value as you require different methods to retrieve this from each database.

The best "databse" solution that has been suggessted is the max() solution although as you have pointed out this is going to return incorrect values if records are added between when you record is added and the max() is called - which is a highly likely scenario if your db will be used with regular db calls.

There is only 1 real solution that I can think will work from an application stand point and that is to use a Data Access Layer where you can call generic commands - for example:

DAL objDal = new DAL(connectionString);
objDal.Connection.Open();
objDal.Connection,ExecuteCommand("insert into abc (x, y, z) values (fx, fy, fx)");
int ident = objDal.Connection.GetIdentityValue()

If you are going to go down this route you need to have some code that wil determine the type of db you are connecting to, and the associated sql commands that are supported by that database for thinsg like retrieving the identity. There are probably data access layers available to download alrady on the internet if you look around.

Regards,

Gary Francis
maXXXeE

ASKER
GaryFrancisLond :
Is it atleast possible to separate the queries from the code. Similar to what can be done in javaEE with hibernate?
That could solve my problem to some extent cos i dont have to make changes to the code.
 I am not looking for all in depth features of hibernate. Just something to separate the db queries from the code, preferable something which exists in asp.net 2 framework itself.
I hope you get the point.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.