你如何使用SCOPE_IDENTIY来检索最后插入的值? [英] How do you use SCOPE_IDENTIY to retrieve last inserted value?
本文介绍了你如何使用SCOPE_IDENTIY来检索最后插入的值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好,我正在为我的项目使用3层架构。我正在尝试使用SCOPE_IDENTITY将最后插入的值检索到我的数据库表中。
我知道如何使用SCOPE_IDENTITY和3层架构实现这一目标?
Hello, i am using the 3 tier architecture for my project. I am trying to retrieve the last inserted value into my database table using the SCOPE_IDENTITY.
Any idea how i can achieve this using SCOPE_IDENTITY AND the 3 tier architecture?
推荐答案
不幸的是,你不能像回复上面Rob的评论一样使用Scope_Identity()。
只有在对具有IDENTITY列的表进行INSERT后才应立即调用Scope_Identity() - 因为它的目的是获取生成的标识值由插入行的数据库引擎。
正如Rob所说,在存储过程中执行此操作通常是最好的方法。如果您使用存储过程,则可以执行以下操作:
Unfortunately, you can't use Scope_Identity() like in your reply to Rob's comment above.
You should only call Scope_Identity() immediately after you've done an INSERT into a table that has an IDENTITY column - since its purpose is to get the identity value generated by the database engine for the inserted row.
As Rob says, doing it in a stored procedure is often the best way. If you use a stored procedure, you could do something like this:
sqlCmd.CommandText = "sp_InsertIntoUploads";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue(...);
.... for all values to store
sqlCmd.Parameters.AddWithValue("@Id", 0).Direction = ParameterDirection.Output;
var id = sqlCmd.ExecuteNonQuery();
和你的存储过程看起来像这样:
And your stored procedure would look like this:
CREATE PROCEDURE dbo.sp_InsertIntoUploads
(
... values to put into the columns...,
@Id INT OUTPUT -- returns the generated id
)
AS
BEGIN
INSERT INTO uploadsTable(columns...)
VALUES (values...)
SELECT @Id = Scope_Identity()
END
GO
如果您更喜欢不使用存储的OUTPUT参数程序你可以使用 ExecuteScalar()
并在sproc中使用 SELECT Scope_Identity()
。
If you prefer not to use OUTPUT parameters from the stored procedure you can use ExecuteScalar()
and in the sproc use SELECT Scope_Identity()
.
这篇关于你如何使用SCOPE_IDENTIY来检索最后插入的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文