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?
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.
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.