What is the difference between ExecuteScalar ExecuteReader and ExecuteNonQuery

Question

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

User · Accepted Answer

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

User · Answer

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    ExecuteReader      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    ExecuteScalar      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    Reference URL   http   nareshkamuni blogspot in 2012 05 what-is-difference-between html

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[asp.net] What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

Examples related to asp.net

Examples related to ado.net