如何使用EF6和SQL Server捕获UniqueKey违例异常？ [英] 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 to 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)
{
    DbUpdateConcurrencyException concurrencyEx = exception as DbUpdateConcurrencyException;
    if (concurrencyEx != null)
    {
        // A custom exception of yours for concurrency issues
        throw new ConcurrencyException();
    }

    DbUpdateException dbUpdateEx = exception as DbUpdateException;
    if (dbUpdateEx != null)
    {
        if (dbUpdateEx.InnerException != null
                && dbUpdateEx.InnerException.InnerException != null)
            {
                SqlException sqlException = dbUpdateEx.InnerException.InnerException as SqlException;
                if (sqlException != null)
                {
                    switch (sqlException.Number)
                    {
                        case 2627:  // Unique constraint error
                        case 547:   // Constraint check violation
                        case 2601:  // Duplicated key row error
                            // Constraint violation exception
                            throw new ConcurrencyException();   // A custom exception of yours for concurrency issues

                        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违例异常？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！