MySql Last Insert ID, Connector .net

I'm using the MySql Connector .net, and I need to get the insert id generated by the last query. Now, I assume the return value of MySqlHelper.ExecuteNonQuery should be the last insert id, but it just returns 1.

The code I'm using is:

int insertID = MySqlHelper.ExecuteNonQuery(Global.ConnectionString, 
"INSERT INTO test SET var = @var", paramArray);

However insertID is always 1. I tried creating a MySql connection and opening/closing manually which resulted in the same behaviour

Solution 1:

Just use LastInsertedId field

MySqlCommand dbcmd = _conn.CreateCommand();
dbcmd.CommandText = sqlCommandString;
dbcmd.ExecuteNonQuery();
long imageId = dbcmd.LastInsertedId;

Solution 2:

1 is the no of records effected by the query here only one row is inserted so 1 returns

for getting id of the inserted row you must use scope_identity() in sqlserver and LAST_INSERT_ID() in MySql

Solution 3:

Try to use this query to get last inserted id -

SELECT LAST_INSERT_ID();

Then, run DbCommand.ExecuteReader method to get IDataReader -

command.CommandText = "SELECT LAST_INSERT_ID()";
IDataReader reader = command.ExecuteReader();

...and get information from the reader -

if (reader != null && reader.Read())
  long id = reader.GetInt64(0);

...do not forget to close the reader;-)

Solution 4:

I had the same problem, and after some testing, I found out that the problem seem to be the connection method; you are using a connection string.

This is of course to make use of the automatic connection pool reuse, but in this case it gave me trouble.

The final solution for me is to create a new connection, execute the insert query, and then execute the last_insert_id(). On the same connection.

Without using the same connection, last_insert_id() might return anything, I don't know why, but guess it looses track of things as it can be different connections.

Example:

MySqlConnection connection = new MySqlConnection(ConnectionString);
connection.Open();

int res = MySqlHelper.ExecuteNonQuery(
    connection,
    "INSERT INTO games (col1,col2) VALUES (1,2);");

object ores = MySqlHelper.ExecuteScalar(
connection,
"SELECT LAST_INSERT_ID();");

if (ores != null)
{
  // Odd, I got ulong here.
  ulong qkwl = (ulong)ores;
  int Id = (int)qkwl;
}

I hope this helps someone!