CODEWITHSUNDEEP
c#mysqlsql-insert
user2181948
user2181948

Reputation: 1938

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

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
}

Upvotes: 1

Views: 542

Answers (1)

jason.kaisersmith
jason.kaisersmith

Reputation: 9650

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

Upvotes: 3

Related Questions