What is the difference between ExecuteScalar and ExecuteNonQuery?

by

ExecuteScalar returns output value where as ExecuteNonQuery does not return any value but the number of rows affected by the query. ExecuteScalar used for fetching a single value and ExecuteNonQuery used to execute Insert and Update statements.

In the context of ASP.NET and database interactions, ExecuteScalar and ExecuteNonQuery are methods provided by ADO.NET for executing SQL commands against a database. Here’s the difference between them:

  1. ExecuteScalar:
    • The ExecuteScalar method is used when you expect your SQL query to return a single value (for example, the result of an aggregate function or a computed value).
    • It returns the value of the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
    • If the result set is empty, ExecuteScalar returns null.
  2. ExecuteNonQuery:
    • The ExecuteNonQuery method is used for executing SQL commands that don’t return a result set, such as INSERT, UPDATE, DELETE, and DDL (Data Definition Language) statements.
    • It returns the number of rows affected by the SQL command.
    • For SELECT statements or other commands that return data, ExecuteNonQuery isn’t appropriate. It’s typically used for executing commands that modify data in the database.

In summary:

  • Use ExecuteScalar when you expect a single value result.
  • Use ExecuteNonQuery when you want to execute commands that modify data and when you don’t need to retrieve data from the database.

Here’s a simple code example:

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Table1", connection);
connection.Open();

// ExecuteScalar example
int rowCount = (int)command.ExecuteScalar();
Console.WriteLine($"Number of rows: {rowCount}");

// ExecuteNonQuery example
command.CommandText = "UPDATE Table1 SET Column1 = 'NewValue' WHERE Column2 = 'SomeValue'";
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"Rows affected by update: {rowsAffected}");
}