asp.net with example - What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?
- will work with Action Queries only (Create,Alter,Drop,Insert,Update,Delete).
- Returns the count of rows effected by the Query.
- Return type is int
- Return value is optional and can be assigned to an integer variable.
- will work with Action and Non-Action Queries (Select)
- Returns the collection of rows selected by the Query.
- Return type is DataReader.
- Return value is compulsory and should be assigned to an another object DataReader.
- will work with Non-Action Queries that contain aggregate functions.
- Return the first row and first column value of the query result.
- Return type is object.
- Return value is compulsory and should be assigned to a variable of required type.
What are the different cases when we use these three? Where should I use one and where should I not?
From the docs (note: MSDN is a handy resource when you want to know what things do!):
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.
Sends the CommandText to the Connection and builds a SqlDataReader.
... and from SqlDataReader ...
Provides a way of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.
You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.
ExecuteReader() executes a SQL query that returns the data provider DBDataReader object that provide forward only and read only access for the result of the query.
ExecuteScalar() is similar to
ExecuteReader() method that is designed for singleton query such as obtaining a record count.
ExecuteNonQuery() execute non query that works with create ,delete,update, insert)
ExecuteNonQuery method will return number of rows effected with INSERT, DELETE or UPDATE operations. This ExecuteNonQuery method will be used only for insert, update and delete, Create, and SET statements. (Read More)
ExecuteScalar will return single row single column value i.e. single value, on execution of SQL Query or Stored procedure using command object. It’s very fast to retrieve single values from database. (Read More)
ExecuteReader will be used to return the set of rows, on execution of SQL Query or Stored procedure using command object. This one is forward only retrieval of records and it is used to read the table values from first to last. (Read More)