Trying to figure out if it's best to use ExecuteScalar
or ExecuteNonQuery
if I want to return the identity column of a newly inserted row. I have read this question and 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:
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
ExecuteNonQuery
here because it is "more proper" for doing inserts? I'm using Visual Studio 2010, .NET 4.0, and SQL Server 2008r2, in case that makes any difference.
Solution 1. ExecuteScalar() only returns the value from the first column of the first row of your query. ExecuteReader() returns an object that can iterate over the entire result set. ExecuteNonQuery() does not return data at all: only the number of rows affected by an insert, update, or delete.
Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.
ExecuteScalar() MethodExecuteScalar() method is used to retrieve a single value from database. It executes the defined query and returns the value in the first column of the first row in the selected result set and ignores all other columns and rows in the result set.
Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations that you need to generate the single value using the data returned by a SqlDataReader.
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: ExecuteScalar
or ExecuteNonQuery
. IMHO, the performance difference between them is so small, that either method is just as "proper".
Having said that, I don't see the point of using ExecuteScalar
if you are grabbing the identity value from an output parameter. In that case, the value returned by ExecuteScalar
becomes useless.
An approach that I like because it requires less code, uses ExecuteScalar
without output parameters:
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With