CODEWITHSUNDEEP
c#
iTayb
iTayb

Reputation: 12743

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

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

Upvotes: 20

Views: 10171

Answers (6)

Rama Krshna Ila
Rama Krshna Ila

Reputation: 496

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

Upvotes: 0

Bobby
Bobby

Reputation: 11576

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.

Upvotes: 30

Adriaan Stander
Adriaan Stander

Reputation: 166326

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.

Upvotes: 15

Oded
Oded

Reputation: 498904

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.

Upvotes: 4

Wael Dalloul
Wael Dalloul

Reputation: 22984

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

Upvotes: 2

Ravi Vanapalli
Ravi Vanapalli

Reputation: 9942

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.

Upvotes: 2

Related Questions