What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

What are the different cases when we use these three? Where should I use one and where should I not?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

  • 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.).

Method 2

ExecuteNonQuery():

  1. will work with Action Queries only (Create,Alter,Drop,Insert,Update,Delete).
  2. Returns the count of rows effected by the Query.
  3. Return type is int
  4. Return value is optional and can be assigned to an integer variable.

ExecuteReader():

  1. will work with Action and Non-Action Queries (Select)
  2. Returns the collection of rows selected by the Query.
  3. Return type is DataReader.
  4. Return value is compulsory and should be assigned to an another object DataReader.

ExecuteScalar():

  1. will work with Non-Action Queries that contain aggregate functions.
  2. Return the first row and first column value of the query result.
  3. Return type is object.
  4. Return value is compulsory and should be assigned to a variable of required type.

Reference URL:

http://nareshkamuni.blogspot.in/2012/05/what-is-difference-between.html

Method 3

Each one is a different type execution.

  • ExecuteScalar is going to be the type
    of query which will be returning a
    single value.

    An example would be returning a generated id after inserting.

    INSERT INTO my_profile (Address) VALUES ('123 Fake St.');
    SELECT CAST(scope_identity() AS int)

  • ExecuteReader gives you a data reader
    back which will allow you to read all
    of the columns of the results a row
    at a time.

    An example would be pulling profile information for one or more users.

    SELECT * FROM my_profile WHERE id = '123456'

  • ExecuteNonQuery is any SQL which
    isn’t returning values, but is
    actually performing some form of work
    like inserting deleting or modifying
    something.

    An example would be updating a user’s profile in the database.

    UPDATE my_profile SET Address = '123 Fake St.' WHERE id = '123456'

Method 4

From the docs (note: MSDN is a handy resource when you want to know what things do!):

ExecuteScalar

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.

ExecuteReader

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.

ExecuteNonQuery

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.

Method 5

To add to what others posted:

ExecuteScalar conceptually returns the leftmost column from the first row of the resultset from the query; you could ExecuteScalar a SELECT * FROM staff, but you’d only get the first cell of the resulting rows
Typically used for queries that return a single value. I’m not 100% sure about SQLServer but in Oracle, you wouldnt use it to run a FUNCTION (a database code that returns a single value) and expect it to give you the return value of the function even though functions return single values.. However, if youre running the function as part of a query, e.g. SELECT SUBSTR(‘abc’, 1, 1) FROM DUAL then it would give the return value by virtue of the fact that the return value is stored in the top leftmost cell of the resulting rowset

ExecuteNonQuery would be used to run database stored procedures, functions and queries that modify data (INSERT/UPDATE/DELETE) or modify database structure (CREATE TABLE…). Typically the return value of the call is an indication of how many rows were affected by the operation but check the DB documentation to guarantee this

Method 6

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)

Method 7

ExecuteNonQuery

This ExecuteNonQuery method will be used only for insert, update and delete, Create, and SET statements.
ExecuteNonQuery method will return number of rows effected with INSERT, DELETE or UPDATE operations.

ExecuteScalar

It’s very fast to retrieve single values from database.
Execute Scalar will return single row single column value i.e. single value, on execution of SQL Query or Stored procedure using command object.
ExecuteReader

Execute Reader 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.

Method 8

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)

Method 9

ExecuteNonQuery: is typically used when there is nothing returned from the Sql statements like insert ,update, delete operations.

cmd.ExcecuteNonQuery();

ExecuteScalar:

It will be used when Sql query returns single value.

Int b = cmd.ExcecuteScalar();

ExecuteReader

It will be used when Sql query or Stored Procedure returns multiple rows/columns

SqlDataReader dr = cmd.ExecuteReader();

for more information you can click here http://www.dotnetqueries.com/Article/148/-difference-between-executescalar-executereader-executenonquery

Method 10

ExecuteNonQuery – Use this operation to execute any arbitrary SQL statements in SQL Server if you do not want any result set to be returned. You can use this operation to create database objects or change
data in a database by executing UPDATE, INSERT, or DELETE statements.

ExecuteScalar- ExecuteScalar() executes SQL statements as well as Stored Procedure and returned a scalar value on first column of first row in the returned Result Set.It is very useful to use with aggregate functions like Count(*) or Sum()

ExecuteReader method is used to execute a SQL Command or storedprocedure returns a set of rows from the database.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x