Get output parameter for ID (primary key)

Question

I have a simple database which has an ID column as primary key, INT, AUTO_INCREMENT and a name column.

I'm trying to insert values into the database and get the ID back. This is part of my code:

using (var connection = new MySqlConnection(...))
{
    connection.Open();
    var command = connection.CreateCommand();

    command.CommandText =
        "INSERT INTO `adressbook`.`Person` (`ID`, `Name`) 
         VALUES (@id, @name);";
    var idParameter = new MySqlParameter("id", MySqlDbType.Int32);
    idParameter.Direction = ParameterDirection.Output;
    idParameter.SourceColumn = "ID";
    command.Parameters.Add(idParameter);
    command.Parameters.AddWithValue("name", "Test");

    command.ExecuteNonQuery();
    var id = command.Parameters["id"].Value;

    connection.Close();
 }

However, I always get NULL as the value, even if I can see that the value has been inserted into the database (so the connection settings etc. are fine).

What I have tried

I have read MySQL Connector/NET Output Parameter Returning NULL and Get the value from Output parameter C#, but it still doesn't work if I change my code to

var reader = command.ExecuteReader();
reader.Read();
reader.Close();

I have read the related post Executing MySqlCommand (StoredProcedure) with output parameter, but it didn't help me since I am already using the correct MySql data type.

Answer 1

Just use the LAST_INSERT_ID function of MySql.

using (var connection = new MySqlConnection(...))
{
    connection.Open();
    var command = connection.CreateCommand();

    command.CommandText = @"INSERT INTO `adressbook`.`Person` (`Name`) VALUES (@name);
                            SELECT LAST_INSERT_ID();";
    command.Parameters.AddWithValue("@name", "Test");
    int result = Convert.ToInt32(command.ExecuteScalar());
    ....... 
 }

As you can see, you can send multiple commands to MySql with a single execution. The LAST_INSERT_ID function returns the last autoincrement value.

However, in this scenario, you need to call ExecuteScalar instead of ExecuteNonQuery because the command will return a single row with a single value.

By the way, if you have an Auto_increment column then don't pass anything for that column.