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.

Jarid

I don't think there is a "generic" way to do it. Error codes aren't unified between DBs – Outnumber

I don't think either about the generic way of the SQL error handling. There are around 36000 errors (error numbers) allocated for SQL Server. See MSDN – Bullion

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.

Indomitability

Ok means that in the static method ExecuteWithNiceException the Exceptions OdbcException, OleDbException, SqlException, OleDbException, OracleException and SqlException should be catched and handled. Because all of these exceptions are derived from DbException. Further, for all of these exceptions the corresponding error number for violation unique key constraint should be evaluated and after that the more meaningful excpetion PrimaryKeyViolationException should be throw. – Jarid

@kbisang: That's the idea. It's tedious work, but you only need to do it once. – Indomitability

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

Amigo

Technically locally generated GUIDs don't have a "strong" guarante to be unique. Thanks to the birthday paradox, if you generate ulong.MaxValue items (2^64 == sqrt(2^sizeof(guid)) == sqrt(2^128)), then you have a 50% chance of one collision. Useless but funny to know (good for interviews). – Outnumber

True there is no way to truly guarantee uniqueness on unconnected machines but given normal usage of almost any system the likelihood of collision is so low that it can be ignored – Amigo

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

Mahatma