使用C#获取插入行的id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/405910/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the id of inserted row using C#
提问by Elie
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:
我有一个查询要在表中插入一行，该表有一个名为 ID 的字段，该字段使用列上的 AUTO_INCREMENT 填充。我需要为下一个功能获取这个值，但是当我运行以下命令时，即使实际值不是 0，它也总是返回 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?
根据我的理解，这应该返回 ID 列，但它每次只返回 0。有任何想法吗？
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"}
采纳答案by Michael Haren
[Edit: added "select" before references to last_insert_id()]
[编辑：在引用 last_insert_id() 之前添加了“选择”]
What about running "select last_insert_id();" after your insert?
select last_insert_id();在插入后运行“ ”怎么样？
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.
编辑：正如 duffymo 所提到的，使用像这样的参数化查询确实可以很好地为您服务。
Edit:Until you switch over to a parameterized version, you might find peace with string.Format:
编辑：在切换到参数化版本之前，您可能会发现 string.Format 的平静：
comm.CommandText = string.Format("{0} '{1}', {2}, {3}, {4}, {5}); select last_insert_id();",
insertInvoice, invoiceDate.ToString(...), bookFee, adminFee, totalFee, customerID);
回答by duffymo
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.
我也很惊讶地看到正在使用字符串连接构建 SQL 查询。我是一名 Java 开发人员，我根本不懂 C#，但我想知道在库中的某个地方是否没有与 java.sql.PreparedStatement 类似的绑定机制？推荐用于防范 SQL 注入攻击。另一个好处是可能的性能好处，因为 SQL 可以被解析、验证、缓存一次和重用。
回答by BFree
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:
实际上，ExecuteScalar 方法返回正在返回的 DataSet 的第一行的第一列。在您的情况下，您只是在执行插入操作，实际上并没有查询任何数据。您需要在插入后查询 scope_identity()（这是 SQL Server 的语法），然后您就会得到答案。看这里：
EDIT:As Michael Haren pointed out, you mentioned in your tag you're using MySql, use last_insert_id(); instead of scope_identity();
编辑：正如迈克尔哈伦指出的那样，你在标签中提到你正在使用 MySql，使用 last_insert_id(); 而不是 scope_identity();
回答by Eli Livshitz
Use LastInsertedId.
使用 LastInsertedId。
View my suggestion with example here: http://livshitz.wordpress.com/2011/10/28/returning-last-inserted-id-in-c-using-mysql-db-provider/
在这里查看我的建议：http: //livshitz.wordpress.com/2011/10/28/returning-last-inserted-id-in-c-using-mysql-db-provider/
回答by petra
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
