問題描述

在 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.據(jù)我所知，前者用于讀取數(shù)據(jù)，后者用于操作數(shù)據(jù).在插入帶有 return 語句的行時，我既在操作又在讀取數(shù)據(jù)，所以我不確定該使用什么.也許我應(yīng)該為此使用完全不同的東西?

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 返回插入到同一范圍內(nèi)的標(biāo)識列中的最后一個標(biāo)識值.范圍是一個模塊:存儲過程、觸發(fā)器、函數(shù)或批處理.因此，如果兩條語句在同一個存儲過程、函數(shù)或批處理中，則它們屬于同一范圍.

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 執(zhí)行插入命令并在一個查詢中檢索新 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();
    }
}

這篇關(guān)于使用 c# 從 SQL Server 插入命令返回值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！