Generic way to catch Unique Key Violation

Question

I use System.Data.Common.DbCommand to insert a new row to database. The point is, that this row already exists.

try
{
    [...]
    DbCommand insertCommand = [...]
    insertCommand.ExecuteScalar();
    [...]
}
catch (System.Exception exception)
{
   [...]
   throw;
}

If I would catch explicitly System.Data.SqlClient.SqlException I could evaluate the ErrorNumber like follow.

try
{
  //insertion code
}
catch(SqlException ex)
{
  if(ex.Number == 2627)
  {
     //Violation of primary key. Handle Exception
  }
}

The ErrorNumber 2627 in the context of a SqlException means Violation of Unique Key. See https://msdn.microsoft.com/en-us/library/ms151757%28v=sql.110%29.aspx

So far so good. Because I am working with DbCommand and therefore with different kinds of Relational Database Management Systems, I am searching for a more generic manner to catch such a violation of unique key constraint.

Thanks for Help.

Answer 1

I'm afraid there is no built-in generic solution for that. As a workaround, you could create a wrapper for Execute...:

public static int ExecuteWithNiceExceptions(this IDbCommand cmd)
{
    try
    {
        return cmd.ExecuteNonQuery();
    }
    catch(SqlException ex)
    {
        if (ex.Number == 2627)
        {
            throw new PrimaryKeyViolationException(cmd, ex);
        }
        else
        {
            throw;
        }
    }
    catch (OleDbException ex)
    {
        ... 
    }
    ...
}

That way, you would "convert" the different, implementation-specific exceptions into generic, meaningful exceptions.

Answer 2

either you should allow the database to assign the key or use a GUID as the key, as database are deigned around multi-user simultaneous access there is no way your code can know what the next key should be so you need to either let the DB manage its own keys or use a key that has guaranteed uniqueness.

however as every database implementation uses their own set of error codes then there is no way to give error codes meaning with out knowing which provider you are using, short of petitioning the ISO for them to create a international standard of error codes, which i doubt will work

Answer 3

I know that the question is about ADO.NET but if you are using EF Core and need a generic way to catch database exceptions that also supports all the major database systems check out EntityFramework.Exceptions