|
In principal you are correct. But remember that exceptions are there for exceptional cases; pun intented.
In the case of creating a new record where you expect the key to be unique, I agree with the others that using an exception is the correct approach. If there is a good chance that the key will not be unique then a different approach would be justified. There I would try to read the record with the key, if it does not exist then I would do the insert. I would however still have the try/catch on the insert.
Panic, Chaos, Destruction.
My work here is done.
or "Drink. Get drunk. Fall over." - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
|
|
|
|
|
Nagy Vilmos wrote: But remember that exceptions are there for exceptional cases
Yes, exactly for exceptional cases. I would use TryParse rather than Try/Catch but be prepared for UnhandledExceptions.
Nagy Vilmos wrote: If there is a good chance that the key will not be unique then a different approach would be justified. There I would try to read the record with the key, if it does not exist then I would do the insert. I would however still have the try/catch on the insert.
Agreed. But the Try/Catch is to catch unexpected cases not the normal flow.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Mark Nischalke wrote: conditions that can be tested for
Do you check your car's battery charge, tire pressure, tread depth, lug nut torque, etc. every time you leave the house? Do you check your fly before leaving the men's room? Do you wear a belt and suspenders?
Just being able to perform a test, doesn't make it worthwhile.
|
|
|
|
|
If I expected my nuts to be loose I certainly would check my fly.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
I'm not sure, but doesn't Sql Server throw a SqlException in these cases? The catch-all could hide conversion-errors, or index-out-of-bounds exceptions.
It's still best to try to be specific about the type of exception that you're trying to handle.
I are Troll 
|
|
|
|
|
Yes only a SqlException is thrown, then you look at the number to determined the type of underlying Exception
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Eddy Vluggen wrote: I'm not sure, but doesn't Sql Server throw a SqlException in these cases? The catch-all could hide conversion-errors, or index-out-of-bounds exceptions.
Yeah, no one here has recommended a catch-all. What was recommended was to catch the specific exception thrown (which depends on what mechanism you are using to talk to the database).
My take on this is that there are no hard and fast rules for things like this. In general, I just try and stick to some consistent practices on a project and these practices themselves may not be identical across multiple projects.
|
|
|
|
|
Nishant Sivakumar wrote: Yeah, no one here has recommended a catch-all
Sorry, I get religious when I see one of those - it comes close to an "on whatevererror resume here".
Nishant Sivakumar wrote: My take on this is that there are no hard and fast rules for things like this
There's this article on CodeProject[^] that seems to suggest that it wouldn't cost much in terms of performance.
An exception that gets eaten OTOH, might be very costly.
Nishant Sivakumar wrote: I just try and stick to some consistent practices on a project and these practices themselves may not be identical across multiple projects.
True. Hell, I'll even color-code the exceptions if there's a compelling reason 
I are Troll
|
|
|
|
|
Eddy Vluggen wrote:
Sorry, I get religious when I see one of those - it comes close to an "on whatevererror resume here".
Yep, absolutely agree.
|
|
|
|
|
Exactly, and your point is... ?
|
|
|
|
|
Programming by error - using an error in the normal flow of an operation. This is using the error trap is incorrect.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Welcome to the code project, what is your iternicene debate of choice today, sir?
|
|
|
|
|
Well, this has certainly been an interesting debate. What nobody has seemed to point out so far is that you actually should combine the two techniques. This is for a simple reason - while you should certainly attempt to detect the unique key violation explicitly, there is no guarantee that this alone will work. The reason for this is simple, presumably your application is going to be multi-user; well, between the time you check the uniqueness and the actual insert occurs, another user could have inputted the same values. So, you have two checks in there - one to cope with the initial check, and a try/catch to cope with the database race condition.
Simple. Job done.
|
|
|
|
|
As I responded prior, I would use TryParse rather than Try/Catch but be prepared for UnhandledExceptions
There is no one size fits all in software development and as the saying goes, they keep building better idiots.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
What does TryParse have to do with attempting to insert a duplicate key?
|
|
|
|
|
The statement is in regard to exception handling in general, which is what the debate is about, not a specific, narrow case of one type of SqlException
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Ah, thanks for clearing that up. I was a touch confused on this.
|
|
|
|
|
Pete O'Hanlon wrote: So, you have two checks in there - one to cope with the initial check, and a try/catch to cope with the database race condition.
I wouldn't do both if the exception handling is going to be necessary anyway. I see no advantage to doing that (e.g., performance would probably be better the exception route anyway). I sometimes see this strategy when dealing with multithreaded applications (e.g., you check a condition that is fast and commonly results in false, then you lock, then you check that condition again). However, I don't think that technique applies here, so I'd just go with the exception handling.
|
|
|
|
|
The reason for the first is simply that you are giving the user information up front that the item already exists. The try/catch is for the really exceptional case where there's a race condition.
|
|
|
|
|
I'd say that depends on the code/application. If the distinction between it being a race condition and it being an old duplicate key is unimportant, then I'd go with exception handling. If the race condition should be handled differently, then it makes sense to do both.
|
|
|
|
|
Pete O'Hanlon wrote: there is no guarantee that this alone will work
10!
|
|
|
|
|
Your problem is not how to deal with the error, the problem is what is the CAUSE of the error. There is no excuse for primary key violation and it is up to the developer to make sure the exception cannot arise in the normal flow of the program.
erdinc27 wrote: if the user tries to add an existing data it gives error like that
I suspect you are inserting instead of updating a record.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Mycroft Holmes wrote: There is no excuse for primary key violation
You're right in that - but the OP didn't have a primary key violation he had a unique constraint violation - which is legitimate.
For example, I am adding new products each of which has a unique id (maybe a GUID or an integer) which will be the primary key.
But the product also requires a human-readable Code - which is also required to be unique.
At some point the user needs to type in the code for a new product - you can check the DB at this point, and tell them to go ahead. Meanwhile another user can try to use the same code, pass the 'does it exist' test, and attempt a commit.
Whoever gets there first will be fine, the 2nd person's commit will fail.
___________________________________________
.\\axxx
(That's an 'M')
|
|
|
|
|
_Maxxx_ wrote: he had a unique constraint violation
Ok I missed that mainly b/c I make no distinction between the 2 constraints, if that scenario existed then I would still do a select on the code field before inserting and feed the result back to the user. In the case of your new product I would return the record/info on the product entered by the first commit.
I admit there is a potential for a conflict on a really high volume system but I have yet to meet such a system.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
