使用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.

您可以使用<一个href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx">SqlCommand.ExecuteScalar执行INSERT命令，并获取新的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屋！