RSS

Returning last inserted id in C# using MySql DB provider

28 Oct

Problem:

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.

Solution:

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

References:

http://www.devart.com/dotconnect/mysql/docs/Parameters.html
http://bugs.mysql.com/bug.php?id=40496

http://www.dotnet247.com/247reference/msgs/9/48912.aspx
http://stackoverflow.com/questions/405910/get-the-id-of-inserted-row-using-c-sharp
http://www.severnsolutions.co.uk/twblog/archive/2003/11/10/mysqlvariables
http://bugs.mysql.com/bug.php?id=59798 

 

Advertisements
 
7 Comments

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.

    Best.

     
  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:
    GRACIAS

     

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: