What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

Why and when different cases when we use these three? Where i can use these three perectly?

  • ExecuteScalar is typically used when your query returns a single value. If it returns more, then the result is the first column of the first row. An example might be SELECT @@IDENTITY AS 'Identity'.
  • ExecuteReader is used for any result set with multiple rows/columns (e.g., SELECT col1, col2 from sometable).
  • ExecuteNonQuery is typically used for SQL statements without results (e.g., UPDATE, INSERT, etc.).

ExecuteNonQuery: Executes a Transact-SQL statement against the connection and returns the number of rows affected.

ExecuteScalar: Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

ExecuteReader: Sends the CommandText to the Connection and builds a SqlDataReader.

 

Execute NonQuery..
1.It will not return any data.
2.It is used with insert and update.
3.It returns only the number of rows affected.

Execute Scaler..
1.It returns only one value.
2.That value will the first column first row value.

Execute Query..
1.Its for command objects.
2.It returns the value given by database through select statement.