使用SqlClient类时如何在SQL Server中获取插入的ID? [英] How can I get inserted ID in SQL Server while using SqlClient class?

问题描述

因此，这是帖子的续篇: 获得插入行身份的最佳方法?

So this is a continuation of post: Best way to get identity of inserted row?

该帖子建议，并且我同意，使用Inserted功能安全地返回插入的id列.

That post proposes, and I agree, to use Inserted feature to safely return inserted id column(s).

在实现此功能时，.net框架的SqlClient似乎不支持此功能，并且在尝试执行命令时失败，但出现以下异常:

While implementing this feature, it seems SqlClient of the .net framework does not support this feature, and fails while trying to execute command, I get the following exception:

System.Data.SqlClient.SqlException:'找不到列"INSERTED"或用户定义的函数或聚合"INSERTED.Id"，或者名称不明确.'

System.Data.SqlClient.SqlException: 'Cannot find either column "INSERTED" or the user-defined function or aggregate "INSERTED.Id", or the name is ambiguous.'

我只是在使用:

return (T)command.ExecuteScalar();

查询位置:

INSERT INTO MyTable 
OUTPUT INSERTED.Id 
(Description) 
VALUES (@Description)

表仅包含

ID (identity int)
Description (varchar(max))

如果不可能做，还有其他安全的方法可以在中间使用不影响性能的变量吗?

If impossible to do, is there other safe way without using variables in the middle that might affect performance?

谢谢

推荐答案

您所做的一切正确，但是您将OUTPUT子句放错了位置:该子句在列列表之后，在VALUES之前，即

You are doing everything correctly, but you have misplaced the OUTPUT clause: it goes after the list of columns and before the VALUES, i.e.

INSERT INTO MyTable (Description) 
OUTPUT INSERTED.Id 
VALUES (@Description)

这篇关于使用SqlClient类时如何在SQL Server中获取插入的ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！