SQL Server 错误处理:异常和数据库-客户端契约 [英] SQL Server error handling: exceptions and the database-client contract

问题描述

我们是一个由 SQL Server 数据库开发人员组成的团队.我们的客户包括 C#/ASP.NET、C# 和 Java 网络服务、Java/Unix 服务和一些 Excel.

We’re a team of SQL Servers database developers. Our clients are a mixed bag of C#/ASP.NET, C# and Java web services, Java/Unix services and some Excel.

我们的客户端开发人员只使用我们提供的存储过程，我们希望(当然，在合理的情况下)他们将它们视为网络服务方法.

Our client developers only use stored procedures that we provide and we expect that (where sensible, of course) they treat them like web service methods.

我们的一些客户开发人员不喜欢 SQL 异常.他们用他们的语言理解它们，但他们不明白 SQL 在我们交流问题的方式上是有限的.

Some our client developers don’t like SQL exceptions. They understand them in their languages but they don’t appreciate that the SQL is limited in how we can communicate issues.

我不只是指 SQL 错误，例如尝试将bob"插入到 int 列中.

I don’t just mean SQL errors, such as trying to insert "bob" into a int column.

我的意思也是例外，比如告诉他们参考值错误，或者数据已经改变，或者他们不能这样做，因为他的聚合不为零.

I also mean exceptions such as telling them that a reference value is wrong, or that data has already changed, or they can’t do this because his aggregate is not zero.

他们真的没有任何具体的替代方案:他们提到我们应该输出参数，但我们假设异常意味着处理停止/回滚.

They’d don’t really have any concrete alternatives: they’ve mentioned that we should output parameters, but we assume an exception means "processing stopped/rolled back.

这里的人们如何处理数据库-客户端合同?一般情况下或数据库和客户端代码猴子之间存在分离的地方.

How do folks here handle the database-client contract? Either generally or where there is separation between the DB and client code monkeys.

• 我们只使用 SQL Server 2005 TRY/CATCH
• 我们已经在回滚到异常表后记录所有错误
• 我们担心我们的一些客户不会检查输出参数并假设一切正常.我们需要标记错误以供支持查看.
• 一切都是例外...客户端需要进行一些消息解析以将信息与错误分开.为了将我们的异常与数据库引擎和调用错误分开，它们应该使用错误号(我们当然都是 50,000)

推荐答案

好吧，我是一个经常处理数据库的客户端代码猴子.这是我的处理方式.

Well I'm a client code monkey that deals with databases a lot. Here's how I handle it.

发生在 SQL 中的异常(引发错误)被传播回调用者.这将包括引用约束、唯一索引违规、更严重的问题等.基本上任何不会使数据操作正常发生的东西都应该传播回来.

Exceptions (raiseerrors) that happen in SQL get propagated back to the caller. This would include ref constraints, unique index violations, more serious issues, etc. Basically anything that would not make the data operation occur normally should be propagated back.

调用者 C# 应该有这个:

The caller C# should have this:

catch (SQLException sqlEx)

然后根据需要处理异常.他们应该有一个特定的 SQLException 处理程序.这很重要.

And then handle the exception as needed. They should have a specific SQLException handler. This is important.

我通常远离输出参数，因为我认为那些与正在传输的数据相关而不是任何错误消息，另外我可以检查 SQL Server 错误代码的异常，所以我们需要的所有数据都应该在那里例外.

I generally stay away from output parameters because I consider those to be related to the data being transported and not any error messages, additionally I can inspect the exception for the SQL Server error code so all the data we need should be in that exception.

此外，在 SQL Server 的某些情况下，我们有可能引发业务类型异常"的存储过程.在这些情况下，我们添加自定义错误号(50000 以上)并在需要时在存储过程中引发该错误.一般来说，我们尽量将这些保持在最低限度，因为它增加了复杂性，但在某些情况下，我们发现它们是必要的.

Additionally, in some cases with SQL Server, we have Stored Procedures that could raise "business type of exceptions". In those cases we add a custom error number (above 50000) and raise that error in the stored procedure when needed. In general we try to keep these at a minimum because it adds complexity, but in some cases, we have found them to be necessary.

现在，由于客户端正在捕获SQLException，他们可以在异常中查看SQL Server返回的错误代码，然后在捕获到异常并且错误号为某个值时采取任何特殊操作(如果需要).如果自定义错误 (>50000) 需要，这允许基于错误代码的二级错误处理.

Now, since the client is catching the SQLException, they can look at the error code returned by SQL Server in the exception and then take any special action (if needed) when the exception is caught and the error number is a certain value. This allows a secondary level of error handling based on the error code if that is required for the custom errors (>50000).

这也允许 DBA 提出自定义错误并让客户端代码以一致的方式来处理它们.然后，DBA 必须告诉客户端代码猴子自定义错误是什么，以便他们可以为它们做好准备.

This also allow the DBAs to raise custom errors and have the client code have a consistent way to deal with them. The DBAs would then have to tell the client code monkey what the custom errors were so they could prepare for them.

我通常不将返回码用于错误处理目的，虽然我可以看到它们是如何使用的，但这意味着代码猴子层中有更多的逻辑来查看和处理返回码.如果它们是一个问题，我想要一个异常返回，因为这样我就可以始终如一地处理它们.如果我还必须查看返回码，现在有多种错误处理途径.

I usually don't use the return codes for error handling purposes, although I can see how they could be used, but that means more logic in the code monkey layer to look at and deal with the return code. If they is a problem, I want an exception back, because then I can deal with them consistently. If I have to look at return codes as well, now there a multiple avenues of error handling.

这篇关于SQL Server 错误处理:异常和数据库-客户端契约的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！