C# 返回标识值时的 ExecuteScalar 与 ExecuteNonQuery
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14246744/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ExecuteScalar vs ExecuteNonQuery when returning an identity value
提问by techturtle
Trying to figure out if it's best to use ExecuteScalaror ExecuteNonQueryif I want to return the identity column of a newly inserted row. I have read this questionand I understand the differences there, but when looking over some code I wrote a few weeks ago (whilst heavily borrowing from this site) I found that in my inserts I was using ExecuteScalar, like so:
试图弄清楚是否最好使用，ExecuteScalar或者ExecuteNonQuery我是否想返回新插入行的标识列。我已经阅读了这个问题并且我了解那里的差异，但是在查看我几周前编写的一些代码时（同时大量从该站点借用），我发现在我使用的插入中ExecuteScalar，如下所示：
public static int SaveTest(Test newTest)
{
var conn = DbConnect.Connection();
const string sqlString = "INSERT INTO dbo.Tests ( Tester , Premise ) " +
" VALUES ( @tester , @premise ) " +
"SET @newId = SCOPE_IDENTITY(); ";
using (conn)
{
using (var cmd = new SqlCommand(sqlString, conn))
{
cmd.Parameters.AddWithValue("@tester", newTest.tester);
cmd.Parameters.AddWithValue("@premise", newTest.premise);
cmd.Parameters.Add("@newId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteScalar();
return (int) cmd.Parameters["@newId"].Value;
}
}
}
This works fine for what I need, so I'm wondering
这对我需要的东西很好用，所以我想知道
- Whether I should be using
ExecuteNonQueryhere because it is "more proper" for doing inserts? - Would retrieving the identity value be the same either way since I'm using an output parameter?
- Are there any performance hits associated with one way or the other?
- Is there generally a better way to do this overall?
- 我是否应该在
ExecuteNonQuery这里使用，因为它“更适合”进行插入？ - 由于我使用的是输出参数，因此检索身份值是否相同？
- 是否有任何与一种方式或另一种方式相关的性能影响？
- 通常有更好的方法来做到这一点吗？
I'm using Visual Studio 2010, .NET 4.0, and SQL Server 2008r2, in case that makes any difference.
我使用的是 Visual Studio 2010、.NET 4.0 和 SQL Server 2008r2，以防万一。
采纳答案by Diego
As suggested by Aaron, a stored procedure would make it faster because it saves Sql Server the work of compiling your SQL batch. However, you could still go with either approach: ExecuteScalaror ExecuteNonQuery. IMHO, the performance difference between them is so small, that either method is just as "proper".
正如 Aaron 所建议的那样，存储过程会使其更快，因为它可以节省 Sql Server 编译 SQL 批处理的工作。但是，您仍然可以使用任何一种方法：ExecuteScalar或ExecuteNonQuery。恕我直言，它们之间的性能差异是如此之小，以至于任何一种方法都是“正确的”。
Having said that, I don't see the point of using ExecuteScalarif you are grabbing the identity value from an output parameter. In that case, the value returned by ExecuteScalarbecomes useless.
话虽如此，ExecuteScalar如果您从输出参数中获取标识值，我看不出使用的意义。在这种情况下，返回的值ExecuteScalar变得无用。
An approach that I like because it requires less code, uses ExecuteScalarwithout output parameters:
我喜欢的一种方法，因为它需要更少的代码，ExecuteScalar不带输出参数使用：
public static int SaveTest(Test newTest)
{
var conn = DbConnect.Connection();
const string sqlString = "INSERT INTO dbo.Tests ( Tester , Premise ) " +
" VALUES ( @tester , @premise ) " +
"SELECT SCOPE_IDENTITY()";
using (conn)
{
using (var cmd = new SqlCommand(sqlString, conn))
{
cmd.Parameters.AddWithValue("@tester", newTest.tester);
cmd.Parameters.AddWithValue("@premise", newTest.premise);
cmd.CommandType = CommandType.Text;
conn.Open();
return (int) (decimal) cmd.ExecuteScalar();
}
}
}
Happy programming!
编程快乐！
EDIT: Note that we need to cast twice: from object to decimal, and then to int(thanks to techturtle for noting this).
编辑：请注意，我们需要投射两次：从 object 到decimal，然后到int（感谢 techturtle 注意到这一点）。
