Returning last inserted id in C# using MySql DB provider

28 Oct


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:


cmd = new MySqlCommand(@"INSERT INTO table (column) " +
                    "VALUES (@value); ", conn);

cmd.Parameters.Add(new MySqlParameter("@value", "xxx"));


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




Posted by on October 28, 2011 in c#, MySql, SQL, Uncategorized, visual studio 2010


7 responses to “Returning last inserted id in C# using MySql DB provider

  1. tony

    February 2, 2012 at 7:34 pm

    just what I was after. Thanks for posting! Tony

  2. panda

    July 1, 2012 at 8:45 pm

    thanks…this was very helpfull

  3. UnboundDarkness

    August 16, 2012 at 12:35 am

    Thanks a lot :D. It helped me a lot. One thing, though. The compiler throws a warning saying the comparison to null has no use since long always return something. Just removed it and everything is fine 😀 (I really hate seeing warnings in my programs).
    Thanks for the help!

  4. Anonymous

    September 14, 2012 at 9:59 pm

    Thank you for this !!!!

    Other sources had more complicated solutions, I just needed: cmd.LastInsertedId

  5. livshitz

    September 27, 2012 at 10:22 am

    You welcome guys.
    If you find something unclear, please let me know.


  6. sobredesarrollo

    October 19, 2012 at 7:14 pm

    you saved my life! thx

  7. sobredesarrollo

    October 19, 2012 at 7:15 pm

    Reblogged this on Tips sobre desarrollo and commented:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: