A common way to catch a unique key violation

Question

I am using System.Data.Common.DbCommand to insert a new row into the database. The fact is that this line already exists.

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

If I explicitly caught System.Data.SqlClient.SqlException, I could evaluate ErrorNumber as shown below.

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

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

So far so good. Since I work with DbCommand and therefore with various types of relational database management systems, I am looking for a more general way to catch this violation of a unique key restriction.

Thanks for the help.

Answer 1

I am afraid there is no built-in universal solution for this. As a workaround, you can 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) { ... } ... } 

In this way, you will "convert" various exceptions to implement into general, significant exceptions.

Answer 2

either you must allow the database to assign a key, or use the GUID as the key, because the database has allowed you to use multi-user simultaneous access, since your code cannot know what the next key should be, so you need to either let the DB manage its own keys or uses a key that has guaranteed uniqueness.

however, since each database implementation uses its own set of error codes, then it is not possible to define error codes without knowing which provider you are using, do not ask ISO in order to create an international standard for error codes, which I doubt will work