C# 如何使用 ExecuteScalar 从插入的行中获取生成的 id？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1336911/
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
How to get the generated id from an inserted row using ExecuteScalar?
提问by andrecarlucci
I know that in OracleI can get the generated id (or any other column) from an inserted row as an output parameter. Ex:
我知道在Oracle 中，我可以从插入的行中获取生成的 id（或任何其他列）作为输出参数。前任：
insert into foo values('foo','bar') returning id into :myOutputParameter
Is there a way to do the same, but using ExecuteScalar instead of ExecuteNonQuery?
有没有办法做同样的事情，但使用 ExecuteScalar 而不是 ExecuteNonQuery？
I don't want to use output parameters or stored procedures.
我不想使用输出参数或存储过程。
ps: I'm using Oracle, not sql server!!!
ps：我使用的是Oracle，而不是 sql server ！！！
采纳答案by Will Marcouiller
Oracle uses sequences as for his identity columns, if we may say so.
如果我们可以这样说，Oracle 使用序列作为他的身份列。
If you have set a sequence for your table primary key, you also have to write a trigger that will insert the Sequence.NextValue or so into your primary key field.
如果您为表主键设置了序列，则还必须编写一个触发器，将 Sequence.NextValue 左右插入到您的主键字段中。
Assuming that you are already familiar with this concept, simply query your sequence, then you will get your answer. What is very practiced in Oracle is to make yourself a function which will return an INT, then within your function, you perform your INSERT. Assuming that you have setup your trigger correctly, you will then be able to return the value of your sequence by querying it.
假设您已经熟悉这个概念，只需查询您的序列，然后您就会得到答案。在 Oracle 中非常实践的是让自己成为一个返回 INT 的函数，然后在您的函数中执行 INSERT。假设您已正确设置触发器，那么您将能够通过查询来返回序列的值。
Here's an instance:
这是一个例子：
CREATE TABLE my_table (
id_my_table INT PRIMARY KEY
description VARCHAR2(100) NOT NULL
)
CREATE SEQUENCE my_table_seq
MINVALUE 1
MAXVALUE 1000
START WITH 1
INCREMENT BY 2
CACHE 5;
If you want to manage the auto-increment yourself, here's how:
如果您想自己管理自动增量，方法如下：
INSERT INTO my_table (
id_my_table,
description
) VALUES (my_table_seq.NEXTVAL, "Some description");
COMMIT;
On the other hand, if you wish not to care about the PRIMARY KEY increment, you may proceed with a trigger.
另一方面，如果您不想关心 PRIMARY KEY 增量，您可以继续使用触发器。
CREATE OR REPLACE TRIGGER my_table_insert_trg
BEFORE INSERT ON my_table FOR EACH ROW
BEGIN
SELECT my_table_seq.NEXTVAL INTO :NEW.id_my_table FROM DUAL;
END;
Then, when you're inserting, you simply type the INSERT statement as follows:
然后，当您插入时，您只需按如下方式键入 INSERT 语句：
INSERT INTO my_table (description) VALUES ("Some other description");
COMMIT;
After an INSERT, I guess you'll want to
插入后，我想你会想要
SELECT my_table_seq.CURRVAL
or something like this to select the actual value of your sequence.
或类似的东西来选择序列的实际值。
Here are some links to help:
以下是一些可以提供帮助的链接：
http://www.orafaq.com/wiki/Sequence
http://www.orafaq.com/wiki/Sequence
http://www.orafaq.com/wiki/AutoNumber_and_Identity_columns
http://www.orafaq.com/wiki/AutoNumber_and_Identity_columns
Hope this helps!
希望这可以帮助！
回答by Christian13467
If you are on oracle, you have to use ExecuteNonQuery and ResultParameter. There is no way to write this as query.
如果您在 oracle 上，则必须使用 ExecuteNonQuery 和 ResultParameter。没有办法把它写成查询。
using (OracleCommand cmd = con.CreateCommand()) {
cmd.CommandText = "insert into foo values('foo','bar') returning id into :myOutputParameter";
cmd.Parameters.Add(new OracleParameter("myOutputParameter", OracleDbType.Decimal), ParameterDirection.ReturnValue);
cmd.ExecuteNonQuery(); // an INSERT is always a Non Query
return Convert.ToDecimal(cmd.Parameters["myOutputParameter"].Value);
}
回答by cranky974
one possible way if one can add one column named "guid" to the table : when inserting one record from c#, generate a guid and write it to the guid column. then perform a select with the generated guid, and you have got the id of inserted record :)
如果可以向表中添加一个名为“guid”的列，一种可能的方法是：从 c# 插入一条记录时，生成一个 guid 并将其写入 guid 列。然后使用生成的 guid 执行选择，您就获得了插入记录的 id :)
回答by fahad
Select t.userid_pk From Crm_User_Info T
Where T.Rowid = (select max(t.rowid) from crm_user_info t)
this will return your required id
这将返回您所需的 ID
回答by Girish Gupta
You can use below code.
您可以使用以下代码。
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO my_table(name, address)
VALUES ('Girish','Gurgaon India')
RETURNING my_id INTO :my_id_param";
OracleParameter outputParameter = new OracleParameter("my_id_param", OracleDbType.Decimal);
outputParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outputParameter);
cmd.ExecuteNonQuery();
return Convert.ToDecimal(outputParameter.Value);
}
