I'm wondering what the best practice is (or the trade offs are) for handling duplicates in a unique index. My pks are 99.9% auto increment integers, so that is never a problem, but I often have a unique index on one or two other fields or combination of fields. What is the preferred method to handle possible duplicates? I have thought of two methods:



- catch the sql exception and notify the user

- check the db first to see if the data would cause a duplicate, using BO methods.



What are you all doing? Why?



Thanks!

We generally query the database with a quick little ExecuteScalar to retrieve the count of records that have the same value.  We don't generally let the exception be thrown because we're always leery letting an exception totally blow away the completion of the call stack, so we try to inform the user of the problem before they get the exception.  Also, you can do the test in the CheckRulesOnCurrentRow so that you can inform the user through a broken rule and tell them they need to change the value.

However, in the off chance that some other user inserts the exact same value in between the business rules being checked and the INSERT into the database, don't forego the use of the unique key on the database (don't just rely on the business rule).  When this happens (window of some <.25 seconds or so depending upon network latency), the exception will be thrown and preserve your database consistency and the user can just try again...

Thanks Ben!