如何使用 EF6 和 SQL Server 捕获 UniqueKey Violation 异常? [英] How can I catch UniqueKey Violation exceptions with EF6 and SQL Server?

问题描述

我的一个表有一个唯一键，当我尝试插入重复记录时，它会按预期抛出异常.但是我需要将唯一键异常与其他异常区分开来，这样我就可以自定义违反唯一键约束的错误消息.

One of my tables have a unique key and when I try to insert a duplicate record it throws an exception as expected. But I need to distinguish unique key exceptions from others, so that I can customize the error message for unique key constraint violations.

我在网上找到的所有解决方案都建议将 ex.InnerException 转换为 System.Data.SqlClient.SqlException 并检查 if Number 属性等于 2601 或 2627 如下:

All the solutions I've found online suggests to cast ex.InnerException to System.Data.SqlClient.SqlException and check the if Number property is equal to 2601 or 2627 as follows:

try
{
    _context.SaveChanges();
}
catch (Exception ex)
{
    var sqlException = ex.InnerException as System.Data.SqlClient.SqlException;

    if (sqlException.Number == 2601 || sqlException.Number == 2627)
    {
        ErrorMessage = "Cannot insert duplicate values.";
    }
    else
    {
        ErrorMessage = "Error while saving data.";
    }
}

但问题是，将 ex.InnerException 转换为 System.Data.SqlClient.SqlException 会导致无效的转换错误，因为 ex.InnerException 是System.Data.Entity.Core.UpdateException 的实际类型，而不是 System.Data.SqlClient.SqlException.

But the problem is, casting ex.InnerException to System.Data.SqlClient.SqlException causes invalid cast error since ex.InnerException is actually type of System.Data.Entity.Core.UpdateException, not System.Data.SqlClient.SqlException.

上面的代码有什么问题?如何捕获违反唯一键约束的行为?

What is the problem with the code above? How can I catch Unique Key Constraint violations?

推荐答案

使用 EF6 和 DbContext API(用于 SQL Server)，我目前正在使用这段代码:

With EF6 and the DbContext API (for SQL Server), I'm currently using this piece of code:

try
{
  // Some DB access
}
catch (Exception ex)
{
  HandleException(ex);
}

public virtual void HandleException(Exception exception)
{
  if (exception is DbUpdateConcurrencyException concurrencyEx)
  {
    // A custom exception of yours for concurrency issues
    throw new ConcurrencyException();
  }
  else if (exception is DbUpdateException dbUpdateEx)
  {
    if (dbUpdateEx.InnerException != null
            && dbUpdateEx.InnerException.InnerException != null)
    {
      if (dbUpdateEx.InnerException.InnerException is SqlException sqlException)
      {
        switch (sqlException.Number)
        {
          case 2627:  // Unique constraint error
          case 547:   // Constraint check violation
          case 2601:  // Duplicated key row error
                      // Constraint violation exception
            // A custom exception of yours for concurrency issues
            throw new ConcurrencyException();
          default:
            // A custom exception of yours for other DB issues
            throw new DatabaseAccessException(
              dbUpdateEx.Message, dbUpdateEx.InnerException);
        }
      }

      throw new DatabaseAccessException(dbUpdateEx.Message, dbUpdateEx.InnerException);
    }
  }

  // If we're here then no exception has been thrown
  // So add another piece of code below for other exceptions not yet handled...
}

正如您提到的 UpdateException，我假设您使用的是 ObjectContext API，但它应该是相似的.

As you mentioned UpdateException, I'm assuming you're using the ObjectContext API, but it should be similar.

这篇关于如何使用 EF6 和 SQL Server 捕获 UniqueKey Violation 异常?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！