[C#/MySQL] Getting the last insert id after commit?

[sathenzar]

I need to grab the last inserted id of a row I'm inserting into a dataset (using the MySQL C#/Connector). I update the database with a mysqldataadapter but I can't find a way to grab the last insert id once the row is commited to the database.

I looked into the debugger and it shows the primary key of the row inserted as {} (in other words null). I don't really want to have to call yet another mysql command to grab it as it will slow everything down I would think. What are my options?

[SuperKid]

The way I done this is by Selecting the ID after because I can't find a way of getting the Id after insert too.

[+Majesticmerc MVC]

I'll admit I don't know much about MySQL, but from what I can find, you need to use the LAST_INSERT_ID() function.

For example:

INSERT INTO my_table (field1, field2, field3)
VALUES (@value1, @value2, @value3);
SELECT LAST_INSERT_ID();

You can find an example here on stackoverflow.com

[sathenzar]

I read the stack overflow link. The only problem is I'm not actually using MySqlCommand (INSERT...). I'm just simply using a dataset, which I in turn use the NewRow() function and add my fields.

After this I call the mysqldataadapter.update(Dataset, string Table) function. It commits the changes to the mysql database but I can't find a way of getting that last insert id without calling a MySqlCommand object to retrieve it.

[sathenzar]

Ok so I'm getting closer. I specify the dataset column as autoincrement = true and unique = true. However now it starts at 1. Is there anyway to make it detect which primary key is the last one? That way it auto starts at where it needs to?

[sathenzar]

Well the problem is what the dataset says isn't what the mysql will always come out as in the primary key. B/c of the lack of documentation on this approach I'm just inventing my own key method.