I was wondering how people were converting FK errors to the business layer then back to the UI.

For example, lets say I have a customer table, with an FK to orders. I have set up my db so that the RI rules restrict deleting a customer that has order records.

So, when I call my Customer_delete SP it returns a sql excecptions with is a FK error.

The only think I could think of that in the data object if we got the FK error code from SQL, we would use a switch statement for all the FK names in the database. For example, if 'FK_Orders_Customer' is in the error message throw BusinessException("You can not delete a customer that has orders.').

The only problem I have with this is the it tightly couples the DataObject to the database.

The idea I considered was to put an extended property on the FK that contained the UserMessage. Once again though, I would have to figure out the contraints name from the message to get the extended property.

Another idea to just have the SP trap for the FK error and raise a custom error with a better end user message.

Anyone have any more generic idea?

Thanks,
BOb