As experimenting the integration of MySql database with c# code I came across to a problem that seemed to be a common issue.
Mostly, after executing an INSERT statement you’d like to get back the new ID of the record.
Intuitively, you would think about creating an output parameter and use @@IDENTITY or scope_identity() or LAST_INSERT_ID() to assign the value to it.
Unfortunately, it seems that when I use this statement: “SET @newID := @@IDENTITY” I get an exception about sql syntax. Reviewing the exception reveals that somehow it repaced the “@newID” with NULL and therefore not allowing execution. (In my case i used SQL statement and not stored procedure).
As stated here, MySql does not support out parameters in the protocol yet.
All the suggested solutions I found didn’t made this work.
MySqlCommand has a member called LastInsertedId which holds the requested variable.
Because I used MySqlParameterCollection to return a collection of the parameters (which was suitable for MS sql) so I added this variable as a parameter. this way I was able to use this variable without changing the way it was implemented (using parameters).
View the example code to get the idea:
//C# cmd = new MySqlCommand(@"INSERT INTO table (column) " + "VALUES (@value); ", conn); cmd.Parameters.Add(new MySqlParameter("@value", "xxx")); cmd.ExecuteNonQuery(); // If has last inserted id, add a parameter to hold it. if (cmd.LastInsertedId != null) cmd.Parameters.Add( new MySqlParameter("newId", cmd.LastInsertedId)); // Return the id of the new record. Convert from Int64 to Int32 (int). return Convert.ToInt32(cmd.Parameters["@newId"].Value);