使用 c# 从 SQL Server Insert 命令返回值 [英] Return value from SQL Server Insert command using c#

问题描述

在 Visual Studio 中使用 C#，我将一行插入到这样的表中:

Using C# in Visual Studio, I'm inserting a row into a table like this:

INSERT INTO foo (column_name)
VALUES ('bar')

我想做这样的事情，但我不知道正确的语法:

I want to do something like this, but I don't know the correct syntax:

INSERT INTO foo (column_name)
VALUES ('bar')
RETURNING foo_id

这将从新插入的行返回 foo_id 列.

This would return the foo_id column from the newly inserted row.

此外，即使我找到了正确的语法，我还有另一个问题:我可以使用 SqlDataReader 和 SqlDataAdapter.据我所知，前者用于读取数据，后者用于操作数据.使用 return 语句插入一行时，我同时在操作和读取数据，所以我不确定该使用什么.也许我应该为此使用完全不同的东西?

Furthermore, even if I find the correct syntax for this, I have another problem: I have SqlDataReader and SqlDataAdapter at my disposal. As far as I know, the former is for reading data, the second is for manipulating data. When inserting a row with a return statement, I am both manipulating and reading data, so I'm not sure what to use. Maybe there's something entirely different I should use for this?

推荐答案

SCOPE_IDENTITY 返回插入到同一范围内的标识列中的最后一个标识值.作用域是一个模块:存储过程、触发器、函数或批处理.因此，如果两个语句在同一个存储过程、函数或批处理中，则它们在同一范围内.

SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

您可以使用 SqlCommand.ExecuteScalar 执行插入命令并在一个查询中检索新 ID.

You can use SqlCommand.ExecuteScalar to execute the insert command and retrieve the new ID in one query.

using (var con = new SqlConnection(ConnectionString)) {
    int newID;
    var cmd = "INSERT INTO foo (column_name)VALUES (@Value);SELECT CAST(scope_identity() AS int)";
    using (var insertCommand = new SqlCommand(cmd, con)) {
        insertCommand.Parameters.AddWithValue("@Value", "bar");
        con.Open();
        newID = (int)insertCommand.ExecuteScalar();
    }
}

这篇关于使用 c# 从 SQL Server Insert 命令返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！