Checking for entry in database before insert

  • Thread starter Thread starter Joey Lee
  • Start date Start date
J

Joey Lee

Hi,

I was wondering which is the best way of checking if a
record exist in the database before inserting.

There are quite a few ways I have seen. Here are some...

1> Insert and hit a primary key violation and then handle
it

2> Select of a dataset/datareader based on the new record
id. If the result has something in it, then don't insert

3> Change the sql statement to count(*) based on the new
record id. If count > 0 then don't insert

So, I was wondering which is the best way(most effecient
way) of doing it. or if there are better way.

Thanks

Joey
 
The best way to handle the situation is optimistic approach, i.e.
" Insert and hit a primary key violation and then handle it"

This is so because :
1. If the record doesnt violate any property then it is inserted without
problems. In passimistic approach you would always check before this step
which is an overhead. Since RDBMS handle concurrency issues optimism is safe
and the best approach.

2. If insertion causes violation you would handle it regardless of the
approach taken. But optimism ignores searching for duplication.

HTH
Ashish
 
In the real world I would do say a customer lookup first. The people
doing the data input would shoot me if the data was already there and
they spent all that time putting in the data only to find out someone
already did this.

Regards,
Jeff
I was wondering which is the best way of checking if a
record exist in the database before inserting.
 
Joey,

In my opinion is the best approac first decide what methode you want to use.

The (very) classic one where you use the datareader, and the system.data
commands with sql string to update insert or to delete.

Or use the dataadapter approach with the dataset/datatable.

In my opinion is it just bad to mix those up.

Just my thought,

Cor
 
How is it that you know the primary key before you insert the record? Are
you using some part of the business information as the primary key?

If this is the case, then as soon as the user has entered the PK info, you
should look up the data and show it to the user. Let them know that the
record exists and that they can update it if they'd like.

Also: it is a good idea not to use business data as part of the primary key.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
 
I very much agree, Cor. I prefer Guids when inserting records.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
 
Back
Top