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).
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.
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.