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.