在捕获SQL .NET唯一键例外 [英] Catching SQL unique key exceptions in .NET

问题描述

我想知道如果任何人有一个更优雅的方式来检查从SQL在.NET等独特的键例外比分析错误信息？现在我打电话SQL存储过程的，然后使用.NET中try catch块。在try catch块我解析错误信息，这是我抛出一个自定义错误的实例调用的类，如果不是我只是把原来的异常调用类的唯一密钥错误。这似乎是非常低效的我。

I was wondering if anyone had a more elegant way to check for unique key exceptions from SQL in .NET other than parsing the error message? Right now I am calling the sproc in SQL, then using a try catch block in .NET. In the try catch block I parse the error message and it is a Unique key error I throw an instance of a custom error to the calling class, if not I just throw the original exception the the calling class. This seems awfully inefficient to me.

推荐答案

如果你赶上的SQLException，你应该能够列举了错误收集控股多家SQLERROR的对象。

If you catch a SqlException, you should be able to enumerate the "Errors" collection holding a number of "SqlError" objects.

中的SQLError有其他的事情属性，如类和号码中 - 唯一键冲突是类= 14，数= 2601检查这些号码找到你的错误precisely

The SqlError has among other things properties like "Class" and "Number" - unique key violations are class = 14 and number = 2601. Check for those numbers to find your error precisely.

这些都是同样的错误codeS你当您尝试执行在SQL Management Studio中查询：

Those are the same error codes you get when you try to execute the query in SQL Management Studio:

消息2601，级别14，状态行1结果
  不能插入重复键行
  对象..........结果
  该语句已终止。

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ..........
The statement has been terminated.

在消息可转换为对SQLERROR的级别到类的编号属性。

The "Msg" translates to the "Number" property on SqlError, the "Level" to "Class".

try
{
  _cmd.ExecuteNonQuery();
}
catch(SqlException sqlExc)
{
   foreach (SqlError error in sqlExc.Errors)
   {
      string msg = string.Format("{0}: {1}", error.Number, error.Message);
   }
}

这样一来，就可以轻松，准确地识别违反唯一约束的错误。

That way, you can easily and EXACTLY identify a "unique constraint violated" error.

马克

这篇关于在捕获SQL .NET唯一键例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！