On creation of a user, a row must be inserted into both the User and Email table. It can fail in either of them(unique constraints). How can I find out which is the reason for failure? My thoughts have been using a lock and querying the database prior to the inserts or parsing the SqlException that comes back(which I'd prefer not to do).
Edit: I should have mentioned this will be running on several machines simultaneously, and I would like it to support different databases.
Edit 2: My solution ended up being using a lock around checking for duplicates. Stored procedures was an option, but I didn't want to place business logic into the database. I commented for others that I was aware of the race conditions in the web farm, but the rarity of the circumstances didn't warrant further work.
Exception handling should be used to capture non-prime scenarios such as the database is down or a command overran the timeout. If you have constraints around User being unique and the Email being unique you should really test for them before you do your submission of data. Relying on check/index constraints as a way to handle these scenarios is going to create confusion in the longrun. Besides, a key best practice in error handling is to never let the end user know the particulars of why an error had occurred.