使用ADO.NET将记录保存到DB时,如何获取PK [英] How do I get the PK when I saved a record into DB with ADO.NET
问题描述
大家好!
我想用ADO.NET将记录保存到SQLServer时获取主键。
我在ADO中找不到功能.NET的对象是这样做的。
我是否再次查询该记录?
有什么好主意吗?
Hi,everyone!
I want get the Primary Key when I saved a record into SQLServer with ADO.NET.
I can't find a FUNCTION in ADO.NET's objects to do this.
Do I query that record again?
Is there any good idea?
推荐答案
基本上你有两个选择:
- 使用SCOPE_IDENTITY():
Basically you have two choices:
- Using SCOPE_IDENTITY():
INSERT INTO [YourTable] ([AColumn]) VALUES (@aColumn);
SELECT SCOPE_IDENTITY();
- 或者使用OUTPUT INSERTED.ID:
- Or using OUTPUT INSERTED.ID:
INSERT INTO [YourTable] ([AColumn])
OUTPUT INSERTED.ID
VALUES (@aColumn);
来自c#的使用:
Usage from c#:
using (SqlConnection connection = /* connection initialization here */) {
using (SqlCommand command = new SqlCommand("INSERT INTO [YourTable] ([AColumn]) VALUES (@aColumn); SELECT SCOPE_IDENTITY();", connection)) {
// OR
using (SqlCommand command = new SqlCommand("INSERT INTO [YourTable] ([AColumn]) OUTPUT INSERTED.ID VALUES (@aColumn);", connection)) {
command.Parameters.AddWithValue("@aColumn", "dummy");
int insertedId = command.ExecuteScalar();
}
}
希望这会有所帮助。
从 SO:如何获取最后插入的ID? [ ^ ]
添加我忘记的查询参数限定[/ Edit]
Hope this helps.
Grabbed from SO: How to get last inserted id?[^]
Add query parameter qualification that I forgot [/Edit]
使用Scope_IDENTITY()可以获得该值。
为此,您必须将查询更改为
Using Scope_IDENTITY() you can get that value.
For that you have to change your query to
"insert into UserInfo(x,x,x,x) values(x,x,x,x);SELECT SCOPE_IDENTITY();"
之后你必须从ExecuteNonQuery更改命令执行( )到ExecuteScalar()
After that you have to change command execution from ExecuteNonQuery() to ExecuteScalar()
Object id = GetSqlCommand(connection, sql, parameters).ExecuteScalar();
现在你得到了ab插入的最后一个主键ove命令。
Now you got the last primary key inserted by above command.
这篇关于使用ADO.NET将记录保存到DB时,如何获取PK的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!