Does ExecuteScalar() have any advantages over ExecuteReader()?

Question

Does ExecuteScalar() have any advantages over ExecuteReader()?

Answer 1

ExecuteScalar only returns the first value from the first row of the dataset. Internal it is treated just like ExecuteReader(), a DataReader is opened, the value is picked and the DataReader gets destroyed afterwards. I also always wondered about that behavior, but it has one advantage: It takes place within the Framework...and you can't compete with the Framework in manners of speed.

Edit By rwwilden: Taking a look with Reflector inside SqlCommand.ExecuteScalar() you can see these lines:

SqlDataReader ds = this.RunExecuteReader(
    CommandBehavior.Default, RunBehavior.ReturnImmediately, true, "ExecuteScalar");
obj2 = this.CompleteExecuteScalar(ds, false);

Exactly what happens inside ExecuteReader. Another advantage is that ExecuteScalar returns null when no data is read. If you use ExecuteReader, you'd have to check this yourself.

Answer 2

From SqlCommand.ExecuteScalar Method

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.

Also from What is the difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar

• ExecuteReader :Use for accessing data. It provides a forward-only, read-only, connected recordset.
• ExecuteNonQuery :Use for data manipulation, such as Insert, Update, Delete.
• ExecuteScalar :Use for retriving 1 row 1 col. value., i.e. Single value. eg: for retriving aggregate function. It is faster than other ways of retriving a single value from DB.