sql-serverweb-serviceserror-handling

How to handle db constraint violations in the user interface?

We implement the majority of our business rules in the database, using stored procs.

I can never decide how best to pass data constraint violation errors from the database back to the user interface. The constraints I'm talking about are tied more to business rules than data integrity.

For example, a db error such as "Cannot insert duplicate key row" is the same as the business rule "you can't have more than one Foo with the same name". But we've "implemented" it at the most common sense location: as a unique constraint that throws an exception when the rule is violated.

Other rules such as "You're only allowed 100 Foos per day" do not cause errors per-say, since they're gracefully handled by custom code such as return empty dataset that the application code checks for and passes back to the ui layer.

And therein lies the rub. Our ui code looks like this (this is AJAX.NET webservices code, but any ajax framework will do):

WebService.AddFoo("foo", onComplete, onError); // ajax call to web service

function onComplete(newFooId) {
    if(!newFooId) {
        alert('You reached your max number of Foos for the day')
        return
    }
    // update ui as normal here
}

function onError(e) {
    if(e.get_message().indexOf('duplicate key')) {
        alert('A Foo with that name already exists');
        return;
    }
    // REAL error handling code here
}

(As a side note: I notice this is what stackoverflow does when you submit comments too quickly: the server generates a HTTP 500 response and the ui catches it.)

So you see, we are handling business rule violations in two places here, one of which (ie the unique constaint error) is being handled as a special case to the code that is supposed to handle real errors (not business rule violations), since .NET propagates Exceptions all the way up to the onError() handler.

This feels wrong. My options I think are:

  1. catch the 'duplicate key violation' exception at the app server level and convert it to whatever it is the ui expects as the "business rule violated" flag,
  2. preempt the error (say, with a "select name from Foo where name = @Name") and return whatever it is the app server expects as the "business rule violated" flag,
  3. in the same ballpark as 2): leverage the unique constraint built into the db layer and blindly insert into Foo, catching any exceptions and convert it to whatever it is the app server expects as the "business rule violated" flag
  4. blindly insert into Foo (like 3) and let that Exception propagate to the ui, plus have the app server raise business rule violations as real Exceptions (as opposed to 1). This way ALL errors are handled in the ui layer's onError() (or similar) code.

What I like about 2) and 3) is that the business rule violations are "thrown" where they are implemented: in the stored proc. What I don't like about 1) and 3) is I think they involve stupid checks like "if error.IndexOf('duplicate key')", just like what is in the ui layer currently.

Edit: I like 4), but most people say to use Exceptions only in exceptional circumstances.

So, how do you people handle propagating business rule violations up to the ui elegantly?

Solution

  • A stored procedure may use the RAISERROR statement to return error information to the caller. This can be used in a way that permits the user interface to decide how the error will appear, while permitting the stored procedure to provide the details of the error.

    RAISERROR can be called with a msg_id, severity and state, and with a set of error arguments. When used this way, a message with the given msg_id must have been entered into the database using the sp_addmessage system stored procedure. This msg_id can be retrieved as the ErrorNumber property in the SqlException that will be raised in the .NET code calling the stored procedure. The user interface can then decide on what sort of message or other indication to display.

    The error arguments are substituted into the resulting error message similarly to how the printf statement works in C. However, if you want to just pass the arguments back to the UI so that the UI can decide how to use them, simply make the error messages have no text, just placeholders for the arguments. One message might be '"%s"|%d' to pass back a string argument (in quotes), and a numeric argument. The .NET code could split these apart and use them in the user interface however you like.

    RAISERROR can also be used in a TRY CATCH block in the stored procedure. That would allow you to catch the duplicate key error, and replace it with your own error number that means "duplicate key on insert" to your code, and it can include the actual key value(s). Your UI could use this to display "Order number already exists", where "x" was the key value supplied.