更新时间:2023-11-14 22:38:34
根据Marcus的建议,我还将研究@@IDENTITY
关键字
进一步阅读 @@ IDENTITY关键字 [
SCOPE_IDENTITY()函数通过与函数调用本身相同作用域的语句返回最后插入的行的标识.
With what Marcus has suggested I would also look at the@@IDENTITY
keyword
further reading @@IDENTITY keyword[^]
--
Actually, the SCOPE_IDENTITY() function would be rather more suitable. If the trigger that blocks the first approach (though I''ve never seen that approach before!) does any inserts in tables with identity columns, @@IDENTITY will reflect the identity of the "globally last inserted" row - NOT the row you care about.
The SCOPE_IDENTITY() function returns the identity of the last inserted row by a statement in the same scope as the function call itself.
您在此处有几个选项.
1)您可以编写一个存储过程,该存储过程使用OUTPUT参数执行插入.成功插入该值后,可以将OUTPUT参数设置为SCOPE_IDENTITY().
2)您可以删除查询的OUTPUT部分,然后在插入成功检索到ID后运行第二个查询.SELECT Max(ID) FROM ImageTable WHERE imageType=''your value'' AND imageName=''your value''
You have a couple of options here.
1) You could write a stored procedure that performs the insert with an OUTPUT parameter. When you have successfully inserted the value you can set the OUTPUT parameter to SCOPE_IDENTITY().
2) You could remove the OUTPUT part of the query and then run a second query after the insert succeeds to retrieve the ID.SELECT Max(ID) FROM ImageTable WHERE imageType=''your value'' AND imageName=''your value''
这是可能的,但是现在您需要使用ExecuteScalar来获取该值.如果性能根本不重要,请改用输出参数.
遵循以下原则:
That is possible, but you now need to use ExecuteScalar to get hold of the value. If performance matters at all, use an output parameter instead.
Along these lines:
cmd.CommandText = "declare @id int OUTPUT; insert T vaules(1, 'abc', 0); set @id = SCOPE_IDENTITY()";
var idParam = cmd.Parameters.AddWithValue("@id", 0);
idParam.Direction = ParameterDirection.Output;
-
还可以考虑使用存储的proc;在许多情况下建议这样做.
--
Also consider using stored procs; it is adviseable in many cases.