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

Does ExecuteScalar() have any advantage开发者_运维问答s over ExecuteReader()?

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.

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.

From ExecuteScalar page on MSDN:

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

So, it's not faster or better, but is used to reduce the amount of code written when only one value is needed.

When you have a single value returned from your Query or SP it's always better to use ExecuteScalar() as it retrieves the first value of the result. Hence, this is faster in this kind of situation.

Execute Scalar intended to get single value from the database while Execute Reader to get multiple records into DataTable.

ExecuteScalar() will take less resources compared to the ExecuteReader() as later will return the multiple column data from the database.

ExecuteReader() will be instantiating the SqlDataReader which is stream based and query the results from the data source