|
hey guys..i added a Unique constraint to a column in my sql table and if the user tries to add an existing data it gives error like that
"Violation of UNIQUE KEY constraint 'ukc_cekilis_no'. Cannot insert duplicate key in object 'dbo.NumaraBilgileri'."
it is ok..but i want to show an error message like that in my program..how i can check if the data already exist
vemedya.com
|
|
|
|
|
You could catch that particular exception. You could use a stored procedure to do the insert that checks first and returns a value that tells you what happened. A third option would be to query before you do the insert, but that is inefficient as you are making two trips to the database instead of just one.
|
|
|
|
|
T M Gray wrote: You could catch that particular exception.
Exceptions are for unexpected events not for normal processing.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
If that were strictly true then there would be no throw method. There would also be no need for subclasses of Exception. If you know what type of Exception to expect then it isn't an unexpected event.
The answers I gave are factually correct. Your statement is a matter of philosophy or preference.
|
|
|
|
|
T M Gray wrote: Your statement is a matter of philosophy or preference.
No, established best practices, architecture guidance and experience.
http://msdn.microsoft.com/en-us/library/seyhszts.aspx[^]
"Know when to set up a try/catch block. For example, you can programmatically check for a condition that is likely to occur without using exception handling. In other situations, using exception handling to catch an error condition is appropriate."
In this case the unique key violation is a known condition that may occur and can be tested for.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
But do you know why it is bad practice? My point is that you shouldn't do something a certain way just because someone says so (even if that someone is Microsoft). Nothing in that article explains why you shouldn't use exceptions for program flow. If you had posted this[^] instead, that at least gives reasons related to performance.
There should be real reasons behind why you code a certain way. If the original poster is not strong in SQL and has no method of source control for database schema then using the stored procedure solution makes it less maintainable. That probably outweighs the performance impact of the Exception use in one method of a small application.
Blindly following a "best practice" without considering the specific situation is a bad idea. Consider this case[^] where it is a choice of one exception or 4 database roundtrips.
Avoid dogma in code.
|
|
|
|
|
T M Gray wrote: But do you know why it is bad practice?
Do you?
Basically your arguments have no merit and are the ramblings of one uneducated in Software Engineering or Software Design principles. Further debate would solve no purpose against such an unprepared person.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
So educate us. Tell us WHY. I laid out facts. You offered no solution to the original poster. You give no explanations. Try and contribute something useful rather than personal attacks.
|
|
|
|
|
There's no hard and fast rules. But I agree with Mark. If a known condition can be tested for without relying on an exception, it's a best course of action. You cannot reliably know the behavior of a system as that system and its dependants rely on a failure mode, especially when any parts of the system get upgraded in the future. What may be an exception in one version may NOT be in the next.
|
|
|
|
|
Dave Kreskowiak wrote: If a known condition can be tested for without relying on an exception, it's a best course of action.
Well, I wouldn't say that's always true. Imagine if TryParse didn't exist. Would you rather Try/Catch a Parse or re-implement a version of parse that returns a bool if the string is invalid? Given the complexity of number formats (e.g., scientific notation), I would just do a Try/Catch. I guess this can be generalized as:
If a known condition is exceptionally complex to test for, then just use exception handling to test if it's valid.
Here's another example. I built a tool that allows the user to enter a regular expression to match against some data. Rather than validate that the regular expression is valid, I just used Try/Catch to catch exceptions thrown by invalid exceptions. I mean I COULD first check if the regular expression is valid, but it would be exceedingly complex and a waste of time when the test for validity already exists in the form of exception handling.
|
|
|
|
|
aspdotnetdev wrote: Imagine if TryParse didn't exist.
It didn't at one point. I said if it could be tested for. Obviously, you can't test for every possible failure in a Parse method. It's not practical since there are just too many possibilities. But, you can test for a single known condition, like a duplicate key.
|
|
|
|
|
Mark Nischalke wrote: In this case the unique key violation is a known condition that may occur and can be tested for.
Every constraint defined on that table is known. Data-types and length, custom constraints, keys. A little further down on the page you're quoting from;
Microsoft wrote: The method you choose depends on how often you expect the event to occur. If the event is truly exceptional and is an error (such as an unexpected end-of-file), using exception handling is better because less code is executed in the normal case.
It becomes philosophy again when you try to define "truly exceptional".
I are Troll 
|
|
|
|
|
Mark Nischalke wrote: In this case the unique key violation is a known condition
Which the database will check anyway; so why check it twice or more? You're just slowing things down needlessly. Especially considering that the internal check by the database is likely to be the quickest.
|
|
|
|
|
Not slowing it down. If you can't account for it otherwise, such as not applying an unique index on a field that may not be unique, then certainly the processing can, and should, be done at the database. If the key already exists return something like false not just let the exception be thrown.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Mark Nischalke wrote: Not slowing it down.
Yes, slowing it down.
<Whoops, I hit post prematurely>
GUI.DoesTheKeyAlreadyExist?
BL.DoesTheKeyAlreadyExist?
API.DoesTheKeyAlreadyExist?
DAL.DoesTheKeyAlreadyExist?
SP.DoesTheKeyAlreadyExist?
DB.DoesTheKeyAlreadyExist?
How many times are you going to check the same darn thing when no problem exists? A duplicate key is an exceptional situation, whether you can test it or not.
Consider database round-trips to be very expensive.
</Whoops, I hit post prematurely>
Mark Nischalke wrote: If the key already exists return something like false
How do I know false means duplicate rather than timeout or a referential integrity violation?
|
|
|
|
|
Please, give me some credit. 
Of course I would not architect such a contrived situation as you have outlined. Neither would I expect false to mean everything. As I, and others, have been saying here, handle the known conditions but be prepared for other cases.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
I believe Mark's argument is that, given that the described error condition is a likely occurrence, then the OP should code to detect and handle the condition directly, rather than rely on the exception mechanism (which is expensive).
Software Zen: delete this;
|
|
|
|
|
|
I was talking out of my hat, there. Generally speaking, exception mechanisms tend to be more expensive than normal control flow. In the SQL environment, that difference may be inconsequential compared to everything else that's going on.
Software Zen: delete this;
|
|
|
|
|
Just being awkward really 
As you say, whatever the code, the SQL hit is going to be the worst and swamp everything else. I agree with Mark's initial thoughts, you should avoid raising exceptions in the first place for the most part, but wouldn't vomit at the try/catch either ( I would avoid a re-throw though).
The one thing I don't think should enter the equation is the performance (ironically, given my post), until it is known that it will adversely impact the system. Early optimisation wrecks good clean code. If there are problems, they are likely to involve a small proportion of the code, which should be optimised as fully as possible. That's my hapney'sworth anyway.
|
|
|
|
|
Gary Wheeler wrote: the described error condition is a likely occurrence
No, it never happens; I've certainly never seen one outside of unit testing -- of exception handling.
|
|
|
|
|
Exactly the point. The application should be designed and coded so that it is an exceptional situation when it occurs. When responding to OP the first option given was to allow the exception to occur, which, IMO, is not correct, it should have been emphasized to correct the problem before it occurs or take other mitigating actions before just accepting the exception.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Uhm, his suggestion sounds pretty good to me.
try
{
// code that attempts an insert
}
catch ( some exception)
{
// show error message to the user about how that key exists
}
What's the big deal?
|
|
|
|
|
I have always been taught and follow the principle that conditions that can be tested for are not exceptions and you shouldn't use exception handling for them. Certainly there are cases you can't test for or don't expect to happen. In this particular case the unique key violation is expected and can be tested for.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Mark Nischalke wrote: I have always been taught and follow the principle that conditions that can be tested for are not exceptions and you shouldn't use exception handling for them. Certainly there are cases you can't test for or don't expect to happen. In this particular case the unique key violation is expected and can be tested for.
If this collision is a rarely occurring one or even an infrequently occurring one, I'd always just go ahead and try and insert and then catch any exception. I wouldn't run an extra database trip just to avoid catching an exception so my code is compatible with somebody's idea of a perfect design.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
