在用户界面中如何处理数据库约束违规？ [英] How to handle db constraint violations in the user interface?

问题描述

我无法决定如何最好地将数据约束违规错误从数据库传回给到用户界面。我所说的约束更多的是与业务规则相比，而不是数据完整性。

例如，诸如无法插入重复键行的数据库错误是相同的作为商业规则，你不能有多个同名的Foo。但是我们已经在最常见的位置实现它：作为一个唯一的约束，当违反规则时会抛出异常。

其他规则，如你每天只允许100个Foos不会导致每个字符的错误，因为它们通过自定义代码（例如返回空数据集）正常处理，应用程序代码检查并返回到ui层。

其中的揉搓。我们的ui代码看起来像这样（这是AJAX.NET webservices代码，但是任何ajax框架都可以）：

  WebService.AddFoo （foo，onComplete，onError）; // ajax调用web服务
 
函数onComplete（newFooId）{
 if（！newFooId）{
 alert（'你达到了最多的Foos的一天） 
 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错误处理代码
} 
  

（作为注意事项：我注意到，当您提交评论太快时，这是什么stackoverflow：服务器生成一个 HTTP 500 响应，并且ui捕获它。）

所以你看，我们正在处理这两个地方的业务规则违规，其中一个（即唯一的constaint错误）被作为一个特殊情况被处理到应该是的代码因为.NET将异常传播到 onError（）处理程序。

来处理真实错误（不是业务规则违规） >

这感觉错了。我认为的选项是：

1. 在应用服务器级别捕获重复键违例异常，转换这是你所期望的商业规则违反旗帜的任何事情，


2. preempt 错误（例如，使用从Foo中选择名称，其中name = @Name），并返回任何应用程序服务器所期望的商业规则违反标志，


3. ballpark作为2）：利用内置到db层的唯一约束，盲目地将插入到Foo 中，捕获任何异常并将其转换为应用程序服务器所期望的业务规则违反了标志


4. 盲目插入到Foo （如3），并将该异常传播到ui， 使应用服务器将业务规则违规提升为真实异常（而不是1）。这样，所有错误都在ui层的 onError（）（或类似的）代码中处理。

我喜欢的2）和3）是商业规则违规被抛出在其中实现：在存储过程中。我不喜欢1）和3）是我认为他们涉及笨蛋检查像如果error.IndexOf（'duplicate key'），就像现在的ui层中的那样。

修改：我喜欢4），但大多数人都说使用例外只在例外情况。

那么，你如何处理传播业务规则违规到ui优雅？

解决方案

存储过程可能会使用RAISERROR语句返回错误信息给来电者。这可以以允许用户界面来决定错误将如何出现的方式使用，同时允许存储过程提供错误的详细信息。

RAISERROR可以使用 msg_id ，严重性和状态以及一组错误参数进行调用。当以这种方式使用时，必须使用sp_addmessage系统存储过程将具有给定的 msg_id 的消息输入数据库。可以将这个 msg_id 作为SqlException中的ErrorNumber属性检索，该属性将在调用存储过程的.NET代码中引发。然后，用户界面可以决定要显示哪种消息或其他指示。

错误参数被替换为生成的错误消息，与语句在C中工作。但是，如果要将参数传回UI，以便UI可以决定如何使用它们，只需使错误消息没有文本，只是参数的占位符。一个消息可能是''％s|％d'传回一个字符串参数（引号）和一个数字参数。 .NET代码可以将它们分开，并在用户界面中使用它们，但您喜欢。

RAISERROR也可以在存储过程中的TRY CATCH块中使用。这将允许您捕获重复键错误，并将其替换为您自己的错误号，这意味着插入上的重复键到您的代码，并且它可以包括实际的键值。您的用户界面可以使用此选项来显示订单号码已经存在，其中x是提供的关键值。

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?

解决方案

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.

这篇关于在用户界面中如何处理数据库约束违规？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！