How to detect SQL INSERT errors for data already in the database?

Question

I have a C# application which uses MySQL to insert data into a database. The application sometimes inserts data that is already in the DB. What is the best way to detect if there were any errors in this case?

I understand that ExecuteNonQuery() returns the number of rows affected, so if this is greater than 0, then that can be interpreted as a successful transaction. However if the data is already in the database, then the rows affected is 0, but this can also be considered a successful transaction since the data is in the DB.

string sql = @"INSERT my_table (field1, field2) VALUES (@value1, @value2);"

MySqlCommand cmd = new MySqlCommand(sql, connection);

// Execute
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
    // Query was successful
}
else
{
    // Query *may* have been successful
}

Not necessarily an answer to the question, but might be a solution. Have you considered using an UPSERT instead? dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html That might return a more useful count for you — Sean Missingham
– Sean Missingham, Commented Jun 28, 2017 at 2:39
Use Select statement to see if the record is already existing in the table and then perform the insert, this way you can even avoid the error check. This is just an alternate for your case. — Sujith
– Sujith, Commented Jun 28, 2017 at 2:46

jason.kaisersmith · Accepted Answer · 2017-06-28 06:02:48Z

Assuming that the data in your database will fail because of a duplicate key then you will need to catch the exception that will be thrown by the SQL command

Then you can look for the error number to determine what caused the error. For a duplicate key I believe the error code is 1062.

try
{
    string sql = @"INSERT my_table (field1, field2) VALUES (@value1, @value2);"
    MySqlCommand cmd = new MySqlCommand(sql, connection);

    int rowsAffected = cmd.ExecuteNonQuery();
    //Do something post insert

}
catch(MySqlException ex)
{
    if (ex.Number == 1062)
    {
      //Duplicate key error.  Data already exists now do something.
    }
}

Full list of MySQL error codes are here: https://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html

Following on from what Sean said above, then you depending on your use case you might want to just overwrite the key anyway (upsert) or ignore the error.

INSERT IGNORE

This command tells Mysql to insert the record if possible. But if a duplicate exists then don't insert but don't throw an error (suppress or ignore the error)

string sql = @"INSERT IGNORE my_table (field1, field2) VALUES (@value1, @value2);"

REPLACE

This command tells MySql to insert the record, if required then replace an existing record with the new data (an Upsert).

string sql = @"REPLACE my_table (field1, field2) VALUES (@value1, @value2);"

This article explains the concepts and ideas around this and goes into more depth: https://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm

Collectives™ on Stack Overflow

How to detect SQL INSERT errors for data already in the database?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related