使用 C# 获取插入行的 id [英] Get the id of inserted row using C#

问题描述

我有一个查询要在表中插入一行，该表有一个名为 ID 的字段，该字段使用列上的 AUTO_INCREMENT 填充.我需要为下一个功能获取这个值，但是当我运行以下命令时，即使实际值不是 0，它也总是返回 0:

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

根据我的理解，这应该返回 ID 列，但它每次只返回 0.有什么想法吗?

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

当我跑步时:

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

我明白了:

{"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"}

推荐答案

在插入后运行select 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());

<小时>

正如 duffymo 提到的，使用参数化查询确实会为您提供很好的服务像这样.

---

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

在切换到参数化版本之前，您可能会发现字符串格式没有问题:

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

这篇关于使用 C# 获取插入行的 id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！