I am curious as to how people trap certain database errors in .NET, particulary foreign key or unique index violations. Coming from a Classic ASP/VB background, my first inclination is to test the error number to then create a friendly message to the user that something is wrong. However, I would think that in the .NET world, there would be things like a SqlForeignKeyException or similar. If such a thing exists, I cannot find it in the documentation anywhere. How do you handle such errors?
+1
A:
If your application is generating Foreign Key or Unique Index violations, there might be something wrong with the App, and its the developers you need to notify about it, not the users. That is, normally validation of the input the user is giving should prevent these sorts of exceptions from being thrown.
A common approach is to catch unhandled errors in the Application_Error event, and log them to a file or database. Also there are tools that can help with error logging, such as ELMAH
For the user, just a generic message would do ... but the Developers should be keeping an eye on the error logs. See Jeff Atwood's post on Exception Driven Development.
Edit: To answer the other part of your question - there are a variety of different Exception objects in .NET, but most SQL Exceptions seem to come through as a generic SqlException class. You find out more about the errors by examining the Message and StackTrace() properties, and also drilling down into the InnerException property. Something like a Foreign Key violation might manifest itself as an HttpException with a SqlException nested inside it.
codeulike
2009-05-09 17:26:35
A good example of what I mean would be for a USERS table with a unique index on LOGIN. Shouldn't there be an easier way to trap the unique violation without testing the error number or doing a SELECT test before the INSERT?
BradBrening
2009-05-09 17:30:52
I see ... you could catch() a SqlException and then check the Message to see if its complaining about a unique index. Some would say that its better to do the SELECT first rather than trap an Exception because Exceptions kick off quite a lot of extra processing. But then theres two sides to every programming argument ... I wouldn't frown at a try{} catch{} in the situation you describe.
codeulike
2009-05-09 17:35:59
I definitely see the side that says that exceptions are for exceptional situations and that you shouldn't depend on them as a "prop" for your logic. Maybe in that light I've been thinking about this issue from the wrong architectural point of view...
BradBrening
2009-05-09 17:41:36
A:
By saying SqlException, you must be using SQL Server-
If you want to know if it was specifically a foreign key error, check the SqlExceptions inner exceptions and check the Number property. This number corresponds to an entry in the master.dbo.sysmessages table, number 0 to 50000 are SQL errors, 50000+ are user defined.
Don't forget to iterate through the collection of errors-- SQL can return more than one.
If you are using the return value, check that as well. You will have to read the TSQL source code to see if this ad hoc style of error reporting was being used.
Warnings are errors of level 10 or less, and must be captured via the SqlInfoMessage event.
If the program is returning the @@error, make sure that it is preserved as @@error is reset to success on the next succeeding command in the batch.
MatthewMartin
2009-05-10 13:34:00