MySql Last Insert ID, Connector .net

Question

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

Answer 1

Just use LastInsertedId field

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

Answer 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

Answer 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;-)

Answer 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!

Answer 5

I know this is an old post, but I have been facing the same issue as Snorvarg. Using MySqlHelper, and using a connection string instead of a Connection object (to allow MySqlHelper to use connection pooling), SELECT LAST_INSERT_ID() would often give me the ID of the previous query that was executed, or other times it would return zero. I would then have to call SELECT LAST_INSERT_ID() a second time to get the correct ID.

My solution was to encapsulate everything between the query that's being executed, and the calling of SELECT LAST_INSERT_ID() in a TransactionScope. This forces MySqlHelper to stick to one connection instead of opening two separate connections.

So:

string sql = "INSERT INTO games (col1,col2) VALUES (1,2);");
string connectionString = "some connection string";

using (TransactionScope scope = new TransactionScope)
{
    int rowsAffected = MySqlHelper.ExecuteNonQuery(connectionString, sql);    
    object id = MySqlHelper.ExecuteScalar(connectionString, "SELECT LAST_INSERT_ID();");
    scope.Complete();
}

Answer 6

try below working solution in repository .

 string query = $"INSERT INTO `users`(`lastname`, `firstname`, `email`, `createdate`, `isdeleted`) " +
                $"VALUES ('{userEntity.LastName}','{userEntity.FirstName}','{userEntity.Email}','{userEntity.CreateDate}',{userEntity.IsDeleted});" +
                $"SELECT LAST_INSERT_ID();";
           var res= _db.ExecuteScalar(query);

            return (int)(UInt64)res;