Usually I have to insert some data in a DB and it can't be inserted because the table has constraints preventing me from doing that. With the app I'm developing, some business rules (like "there are not two persons with the same id type and number" or "the XXXX product is already registered") are enforced with UNIQUE or composite keys and other mechanisms. Although I know that DBMS throws an error message (like ORA-6346 or ) I do not know how to catch those errors in .net 4.0 and translate them to an error that can be meaningful for the business layer.
通常我必须在数据库中插入一些数据并且无法插入,因为该表具有阻止我这样做的约束。使用我正在开发的应用程序,一些业务规则(例如“没有两个具有相同ID类型和数量的人”或“XXXX产品已经注册”)是使用UNIQUE或复合键和其他机制强制执行的。虽然我知道DBMS会抛出错误消息(如ORA-6346或),但我不知道如何在.net 4.0中捕获这些错误并将其转换为对业务层有意义的错误。
As an example: I've seen an insertion mechanism that asks the DB if the register already exists and then it proceeds to insert data if it isn't the case. I want to do this only using a query and catching the database constraint violation error because the first way seems to me as very inefficient (DB can alert you about duplication with an error).
作为一个例子:我已经看到一个插入机制,询问数据库是否已存在寄存器,然后如果不是这样,则继续插入数据。我想仅使用查询并捕获数据库约束违规错误来执行此操作,因为第一种方式在我看来效率非常低(DB可以提醒您有关重复的错误)。
How can I implement something like that?
我怎么能实现这样的东西?
Note: I think that it is possible to catch the exception from the database and use its ORA-xxxx code to try to figure out what has happened. I do not remember with precision if the error message shows which constraint (the name of...) has been broken, but business layer code can contain constants with the constraint names and, from them, know what has happened.
注意:我认为可以从数据库中捕获异常并使用其ORA-xxxx代码来试图弄清楚发生了什么。如果错误消息显示哪个约束(名称...)已被破坏,我不记得精确,但业务层代码可以包含具有约束名称的常量,并从中了解发生了什么。
5 个解决方案
#1
7
What you should do here depends really on the architecture of your system, and your attitude towards the placement of business logic.
你应该在这里做什么取决于你的系统架构,以及你对业务逻辑布局的态度。
Many systems architects prefer to use a database as a dumb data store, and implement the type of error handling and integrity checking you're talking about in the middle/application layer. This is a perfectly valid approach, and is particularly suited to systems requiring regular smaller releases, where the business logic is subject to regular change (its much easier to redistribute an executable midweek than to co-ordinate a database release), and where the data model is fairly simple.
许多系统架构师更喜欢将数据库用作哑数据存储,并实现您在中间/应用程序层中讨论的错误处理和完整性检查的类型。这是一种非常有效的方法,特别适用于需要定期更小版本的系统,其中业务逻辑需要定期更改(在周中重新分发可执行文件比协调数据库版本更容易),以及数据的位置模型很简单。
The other approach is to put some well-defined semi-permanent business logic into the database layer. This is particularly powerful when the data model is more complex, and you have a good DBA! ;)
另一种方法是将一些定义明确的半永久业务逻辑放入数据库层。当数据模型更复杂,并且你有一个好的DBA时,这个功能特别强大! ;)
My personal opinion is, an enterprise database should be responsible for its own integrity, and so I prefer to have logic in the database layer to ensure this - removing any vulnerability to bugs being introduced in non-database code releases. So in your specific example, I would definitely catch the error and report it meaningfully to your application layer.
我个人认为,企业数据库应该对其自身的完整性负责,因此我更喜欢在数据库层中使用逻辑来确保这一点 - 消除在非数据库代码版本中引入的错误的任何漏洞。因此,在您的具体示例中,我肯定会捕获错误并将其有意义地报告给您的应用程序层。
Oracle supports catching various types of error using names exceptions, allowing you to raise these exceptions to your applications in a meaningful way. For example:
Oracle支持使用名称异常捕获各种类型的错误,允许您以有意义的方式将这些异常引发到应用程序。例如:
PROCEDURE test() AS
b VARCHAR2;
BEGIN
-- if the following row exists, then DUP_VAL_ON_INDEX will be thrown
-- (assuming there is a primary key constraint)
INSERT INTO table(a,b,c)
VALUES(1,2,3);
-- if there is no matching record, NO_DATA_FOUND will be thrown
SELECT a
INTO b
FROM TABLE
WHERE c = 'blah';
EXCEPTION -- both types of exception can be caught and embellished
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error(-20570, 'Attempted to insert a duplicate value', TRUE);
WHEN NO_DATA_FOUND THEN
raise_application_error(-20571, 'No matching row in table for value:' || 'blah', TRUE);
WHEN OTHERS THEN
rollback
END test;
You can find more information here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm
您可以在此处找到更多信息:http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm
Hope this helps..
希望这可以帮助..
#2
3
There's a couple of approaches, here's broadly what I would do:
有几种方法,这就是我要做的事情:
- Let the error bubble up from the DB call back into your managed code.
- Use a component to examine the error message provided by SQL, and identify the corresponding "user/business layer friendly" message.
让错误从数据库调用回升到托管代码。
使用组件检查SQL提供的错误消息,并标识相应的“用户/业务层友好”消息。
I agree with Mellamokb that error handling can be done within the stored proc but that doesn't exactly fit with your scenario as you specifically want to provide something that the business layer understands - which by definition the data layer should never know.
我同意Mellamokb的说法,错误处理可以在存储过程中完成,但这并不完全适合您的场景,因为您特别想要提供业务层理解的东西 - 根据定义,数据层永远不会知道。
For #2, the MS Enterprise Libraries have an error handling block which (I think) allows you to that sort of thing through config; or if not it might get you close.
对于#2,MS企业库有一个错误处理块,我认为它允许你通过配置进行那种事情;如果没有,它可能会让你接近。
#3
1
I had been thinking about the same thing recently. I made an extension method which takes the Message from a SqlException and translates it to something more useful to an end-user using regular expressions to extract the useful information from the error message and String.Format to put that information into the new message.
我最近一直在考虑同样的事情。我创建了一个扩展方法,它从SqlException获取Message并将其转换为对最终用户更有用的东西,使用正则表达式从错误消息中提取有用信息,并将String.Format放入新消息中。
I used a second dictionary to look up constraint names found by the regular expression and translate those to an English description of the constraint violated.
我使用第二个字典查找正则表达式找到的约束名称,并将其转换为违反约束的英文描述。
This SQL error message:
此SQL错误消息:
Violation of UNIQUE KEY constraint 'uniq_ticket'. Cannot insert duplicate key in object 'dbo.TicketHeader'. The statement has been terminated.
违反UNIQUE KEY约束'uniq_ticket'。无法在对象'dbo.TicketHeader'中插入重复键。该语句已终止。
Returns this result:
返回此结果:
Save to table dbo.TicketHeader failed: Ticket number must be unique.
保存到表dbo.TicketHeader失败:票号必须是唯一的。
I would imagine it could work very similarly for exceptions sent by Oracle.
我认为对于Oracle发送的异常,它可以非常相似。
public static class SqlExceptionExtension
{
private static readonly Dictionary<string, string> Messages;
private static readonly Dictionary<string, string> Constraints;
static SqlExceptionExtension()
{
Messages = new Dictionary<string, string> {{@"Violation of UNIQUE KEY constraint '(?<Constraint>.*)'. Cannot insert duplicate key in object '(.*)'. The statement has been terminated.", "Save to table {2} failed: {0}"}};
Constraints = new Dictionary<string, string> { { "uniq_ticket", "Ticket number must be unique." } };
}
public static string BusinessLayerMessage(this Exception e)
{
foreach(var reg in Messages.Keys)
{
var match = Regex.Match(e.Message, reg);
if (match.Success)
{
string friendlyConstraint = "";
if (match.Groups["Constraint"] != null)
{
friendlyConstraint = Constraints[match.Groups["Constraint"].Value] ??
match.Groups["Constraint"].Value;
}
var groups = match.Groups.Cast<Group>().Select(x => x.Value);
var strings = new [] {friendlyConstraint};
return String.Format(Messages[reg], strings.Concat(groups).ToArray());
}
}
return "Unexpected Database error.";
}
}
}
I'd imagine you could do this on the individual SqlError or OracleErrors included in your Exception for more reliable results, this was just a proof of concept.
我想你可以在你的Exception中包含的单个SqlError或OracleErrors上执行此操作,以获得更可靠的结果,这只是一个概念证明。
#4
0
A method I know of and have used is to perform the same validations yourself and return useful error codes to your application. So for example, if you have a stored procedure that inserts a record to the database, it should also check if the constraints will all be satisfied, and if not, return an appropriate error code:
我知道并使用过的方法是自己执行相同的验证并将有用的错误代码返回给您的应用程序。因此,例如,如果您有一个将记录插入数据库的存储过程,它还应检查是否所有约束都得到满足,如果没有,则返回相应的错误代码:
(pseudo-sql)
function create-user
@username, @password, @name, @email
as
if @username already exists return 1 --duplicate username
if @email already exists return 2 --duplicate email
insert user values (@username, @password, @name, @email)
return 0 -- success
#5
0
If you're looking for inspiration, Have a look at how NHibernate handles this with its ISQLExceptionConverter interface. You can see a sample implementation here.
如果您正在寻找灵感,请查看NHibernate如何通过其ISQLExceptionConverter接口处理此问题。您可以在此处查看示例实现。
#1
7
What you should do here depends really on the architecture of your system, and your attitude towards the placement of business logic.
你应该在这里做什么取决于你的系统架构,以及你对业务逻辑布局的态度。
Many systems architects prefer to use a database as a dumb data store, and implement the type of error handling and integrity checking you're talking about in the middle/application layer. This is a perfectly valid approach, and is particularly suited to systems requiring regular smaller releases, where the business logic is subject to regular change (its much easier to redistribute an executable midweek than to co-ordinate a database release), and where the data model is fairly simple.
许多系统架构师更喜欢将数据库用作哑数据存储,并实现您在中间/应用程序层中讨论的错误处理和完整性检查的类型。这是一种非常有效的方法,特别适用于需要定期更小版本的系统,其中业务逻辑需要定期更改(在周中重新分发可执行文件比协调数据库版本更容易),以及数据的位置模型很简单。
The other approach is to put some well-defined semi-permanent business logic into the database layer. This is particularly powerful when the data model is more complex, and you have a good DBA! ;)
另一种方法是将一些定义明确的半永久业务逻辑放入数据库层。当数据模型更复杂,并且你有一个好的DBA时,这个功能特别强大! ;)
My personal opinion is, an enterprise database should be responsible for its own integrity, and so I prefer to have logic in the database layer to ensure this - removing any vulnerability to bugs being introduced in non-database code releases. So in your specific example, I would definitely catch the error and report it meaningfully to your application layer.
我个人认为,企业数据库应该对其自身的完整性负责,因此我更喜欢在数据库层中使用逻辑来确保这一点 - 消除在非数据库代码版本中引入的错误的任何漏洞。因此,在您的具体示例中,我肯定会捕获错误并将其有意义地报告给您的应用程序层。
Oracle supports catching various types of error using names exceptions, allowing you to raise these exceptions to your applications in a meaningful way. For example:
Oracle支持使用名称异常捕获各种类型的错误,允许您以有意义的方式将这些异常引发到应用程序。例如:
PROCEDURE test() AS
b VARCHAR2;
BEGIN
-- if the following row exists, then DUP_VAL_ON_INDEX will be thrown
-- (assuming there is a primary key constraint)
INSERT INTO table(a,b,c)
VALUES(1,2,3);
-- if there is no matching record, NO_DATA_FOUND will be thrown
SELECT a
INTO b
FROM TABLE
WHERE c = 'blah';
EXCEPTION -- both types of exception can be caught and embellished
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error(-20570, 'Attempted to insert a duplicate value', TRUE);
WHEN NO_DATA_FOUND THEN
raise_application_error(-20571, 'No matching row in table for value:' || 'blah', TRUE);
WHEN OTHERS THEN
rollback
END test;
You can find more information here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm
您可以在此处找到更多信息:http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm
Hope this helps..
希望这可以帮助..
#2
3
There's a couple of approaches, here's broadly what I would do:
有几种方法,这就是我要做的事情:
- Let the error bubble up from the DB call back into your managed code.
- Use a component to examine the error message provided by SQL, and identify the corresponding "user/business layer friendly" message.
让错误从数据库调用回升到托管代码。
使用组件检查SQL提供的错误消息,并标识相应的“用户/业务层友好”消息。
I agree with Mellamokb that error handling can be done within the stored proc but that doesn't exactly fit with your scenario as you specifically want to provide something that the business layer understands - which by definition the data layer should never know.
我同意Mellamokb的说法,错误处理可以在存储过程中完成,但这并不完全适合您的场景,因为您特别想要提供业务层理解的东西 - 根据定义,数据层永远不会知道。
For #2, the MS Enterprise Libraries have an error handling block which (I think) allows you to that sort of thing through config; or if not it might get you close.
对于#2,MS企业库有一个错误处理块,我认为它允许你通过配置进行那种事情;如果没有,它可能会让你接近。
#3
1
I had been thinking about the same thing recently. I made an extension method which takes the Message from a SqlException and translates it to something more useful to an end-user using regular expressions to extract the useful information from the error message and String.Format to put that information into the new message.
我最近一直在考虑同样的事情。我创建了一个扩展方法,它从SqlException获取Message并将其转换为对最终用户更有用的东西,使用正则表达式从错误消息中提取有用信息,并将String.Format放入新消息中。
I used a second dictionary to look up constraint names found by the regular expression and translate those to an English description of the constraint violated.
我使用第二个字典查找正则表达式找到的约束名称,并将其转换为违反约束的英文描述。
This SQL error message:
此SQL错误消息:
Violation of UNIQUE KEY constraint 'uniq_ticket'. Cannot insert duplicate key in object 'dbo.TicketHeader'. The statement has been terminated.
违反UNIQUE KEY约束'uniq_ticket'。无法在对象'dbo.TicketHeader'中插入重复键。该语句已终止。
Returns this result:
返回此结果:
Save to table dbo.TicketHeader failed: Ticket number must be unique.
保存到表dbo.TicketHeader失败:票号必须是唯一的。
I would imagine it could work very similarly for exceptions sent by Oracle.
我认为对于Oracle发送的异常,它可以非常相似。
public static class SqlExceptionExtension
{
private static readonly Dictionary<string, string> Messages;
private static readonly Dictionary<string, string> Constraints;
static SqlExceptionExtension()
{
Messages = new Dictionary<string, string> {{@"Violation of UNIQUE KEY constraint '(?<Constraint>.*)'. Cannot insert duplicate key in object '(.*)'. The statement has been terminated.", "Save to table {2} failed: {0}"}};
Constraints = new Dictionary<string, string> { { "uniq_ticket", "Ticket number must be unique." } };
}
public static string BusinessLayerMessage(this Exception e)
{
foreach(var reg in Messages.Keys)
{
var match = Regex.Match(e.Message, reg);
if (match.Success)
{
string friendlyConstraint = "";
if (match.Groups["Constraint"] != null)
{
friendlyConstraint = Constraints[match.Groups["Constraint"].Value] ??
match.Groups["Constraint"].Value;
}
var groups = match.Groups.Cast<Group>().Select(x => x.Value);
var strings = new [] {friendlyConstraint};
return String.Format(Messages[reg], strings.Concat(groups).ToArray());
}
}
return "Unexpected Database error.";
}
}
}
I'd imagine you could do this on the individual SqlError or OracleErrors included in your Exception for more reliable results, this was just a proof of concept.
我想你可以在你的Exception中包含的单个SqlError或OracleErrors上执行此操作,以获得更可靠的结果,这只是一个概念证明。
#4
0
A method I know of and have used is to perform the same validations yourself and return useful error codes to your application. So for example, if you have a stored procedure that inserts a record to the database, it should also check if the constraints will all be satisfied, and if not, return an appropriate error code:
我知道并使用过的方法是自己执行相同的验证并将有用的错误代码返回给您的应用程序。因此,例如,如果您有一个将记录插入数据库的存储过程,它还应检查是否所有约束都得到满足,如果没有,则返回相应的错误代码:
(pseudo-sql)
function create-user
@username, @password, @name, @email
as
if @username already exists return 1 --duplicate username
if @email already exists return 2 --duplicate email
insert user values (@username, @password, @name, @email)
return 0 -- success
#5
0
If you're looking for inspiration, Have a look at how NHibernate handles this with its ISQLExceptionConverter interface. You can see a sample implementation here.
如果您正在寻找灵感,请查看NHibernate如何通过其ISQLExceptionConverter接口处理此问题。您可以在此处查看示例实现。