Get the id of inserted row using C#

Question

I have a query to insert a row into a table, which has a field called ID, which is populated using an AUTO_INCREMENT on the column. I need to get this value for the next bit of functionality, but when I run the following, it always returns 0 even though the actual value is not 0:

MySqlCommand comm = connect.CreateCommand();
comm.CommandText = insertInvoice;
comm.CommandText += "\'" + invoiceDate.ToString("yyyy:MM:dd hh:mm:ss") + "\', " + bookFee + ", " + adminFee + ", " + totalFee + ", " + customerID +  ")";
int id = Convert.ToInt32(comm.ExecuteScalar());

According to my understanding, this should return the ID column, but it just returns 0 every time. Any ideas?

EDIT:

When I run:

"INSERT INTO INVOICE (INVOICE_DATE, BOOK_FEE, ADMIN_FEE, TOTAL_FEE, CUSTOMER_ID) VALUES ('2009:01:01 10:21:12', 50, 7, 57, 2134);last_insert_id();"

I get:

{"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'last_insert_id()' at line 1"}

Cantone

1. Can you post the final CommandText that gets executed? 2. Are the records actually being inserted? – Bobsleigh

I posted the query, the error, and yes, the rows are being inserted. – Cantone

OK, how about "SELECT last_insert_id();" at the end? – Bobsleigh

Answer 1

[Edit: added "select" before references to last_insert_id()]

What about running "select last_insert_id();" after your insert?

MySqlCommand comm = connect.CreateCommand();
comm.CommandText = insertInvoice;
comm.CommandText += "\'" + invoiceDate.ToString("yyyy:MM:dd hh:mm:ss") + "\', "  
    + bookFee + ", " + adminFee + ", " + totalFee + ", " + customerID +  ");";
    + "select last_insert_id();"

int id = Convert.ToInt32(comm.ExecuteScalar());

---

Edit: As duffymo mentioned, you really would be well served using parameterized queries like this.

---

Edit: Until you switch over to a parameterized version, you might find peace with string.Format:

comm.CommandText = string.Format("{0} '{1}', {2}, {3}, {4}, {5}); select last_insert_id();",
  insertInvoice, invoiceDate.ToString(...), bookFee, adminFee, totalFee, customerID);

Bobsleigh

I'd try the query without it. Are the records actually being inserted? – Bobsleigh

OK, how about "SELECT last_insert_id();" at the end? – Bobsleigh

Michael: last_insert_id() is not thread safe. Is another thread is inserting things, the last_insert_id will return the last inserted id on the connection to the db. So if several threads do it (or even separate processes with the same user to the db), it will be faulty. – Phonograph

Just wanted to note that Ted is correct if the individual threads are using a shared connection. If each thread is establishing it's own connection it should function just fine, since mysql handles it on a per-connection basis. last_insert_id() documentation – Cook

Answer 2

MySqlCommand comm = connect.CreateCommand();
comm.CommandText = insertStatement;  // Set the insert statement
comm.ExecuteNonQuery();              // Execute the command
long id = comm.LastInsertedId;       // Get the ID of the inserted item

Tremain

LastInsertedId is not thread safe. If another thread is inserting things, the LastInsertedId will return the last inserted id on the connection to the db. So if several threads do it (or even separate processes with the same user to the db), it will be faulty. – Phonograph

Awesome.. I even did not think about that MysqlData would be providing it. great. – Figurate

It might be that cmd.LastInsertedId is not thread safe.....but I assume the ID is retrieved in the same one ExecuteNonQuery call, not two query calls as you have to do with SELECT LAST_INSERT_ID(). Is it really possible for another call from another thread to get "in between" the inserted row and the retrieving of the ID. I guess the server do this task and therefore do immediately. Is LAST_INSERT_ID thread safe? – Concessionaire

The LastInsertedId is returned immediately with the cmd.executequery, so I really don't think another thread can get in-between this. But I would also like to know to be 100% sure. But at least we know that it is safe from other connections insertions – Evolve

Seems like it is safe. See: https://mcmap.net/q/491599/-thread-safety-of-mysql-39-s-select-last_insert_id – Interment

Answer 3

Use LastInsertedId.

View my suggestion with example here: http://livshitz.wordpress.com/2011/10/28/returning-last-inserted-id-in-c-using-mysql-db-provider/

Substance

Answer 4

It bothers me to see anybody taking a Date and storing it in a database as a String. Why not have the column type reflect reality?

I'm also surprised to see a SQL query being built up using string concatenation. I'm a Java developer, and I don't know C# at all, but I'd wonder if there wasn't a binding mechanism along the lines of java.sql.PreparedStatement somewhere in the library? It's recommended for guarding against SQL injection attacks. Another benefit is possible performance benefits, because the SQL can be parsed, verified, cached once, and reused.

Katy

OP might be using a date column in the DB--we can't see that part. I agree that you should use parameterized queries, though. Thankfully it doesn't look like you are inserting any text so strictly speaking you're probably safe on this one (though performance could be better). – Bobsleigh

column is actually a date, but if I try inserting the date object directly, I had issues with it resetting the date to a set of 0s. I'm new to C#, but I agree with you, there likely is a version of PreparedStatement for C#, and I will change that. – Cantone

If the column is indeed a date, what's that "toString" call with the formatting pattern doing in the code? – Katy

Also regarding the binding: Odbc has binding mechanisms for mysql see dev.mysql.com/doc/connector-odbc/en/… and #18083340 – Segno

Answer 5

Actually, the ExecuteScalar method returns the first column of the first row of the DataSet being returned. In your case, you're only doing an Insert, you're not actually querying any data. You need to query the scope_identity() after you're insert (that's the syntax for SQL Server) and then you'll have your answer. See here:

Linkage

EDIT: As Michael Haren pointed out, you mentioned in your tag you're using MySql, use last_insert_id(); instead of scope_identity();

Phenacite

My answer includes the scope_identity version for Mysql, as per the tags. – Bobsleigh

Oh, my mistake, I didn't notice the MySql tag. I'll edit my post. – Phenacite

No problem--i miss them often enough myself. – Bobsleigh