When to use ExecuteScalar, ExecuteReader, and ExecuteNonQuery? [closed]

Question

• ExecuteScalar() only returns the value from the first column of the first row of your query.
• ExecuteReader() returns an object that can iterate over the entire result set while only keeping one record in memory at a time.
• ExecuteNonQuery() does not return data at all: only the number of rows affected by an insert, update, or delete.

Additionally, you can look at the DbDataAdapter type, which includes a Fill() method that allows you to download the entire resultset into a DataTable or DataSet object, as well as a number of other abilities.

Finally, this seems like a good time for you to get familiar with Microsoft Docs. This is what documentation is made for: you have the method names; go look them up.

ExecuteScalar : For Single Value

 Int32 Value = Convert.ToInt32(ExecuteScalar("SELECT SUM(COLUMNNAME) FROM TABLE")); 
 Int32 Value = Convert.ToInt32(ExecuteScalar("SELECT AVG(COLUMNNAME) FROM TABLE")); 

ExecuteReader : Row reading in forward mode

 IdataReader dr = ExecuteReader("SELECT * FROM TABLE"); 
 while(dr.Read())
 {
     //You will get rows values like this dr["ColumnName"]
 } 

ExecuteNonQuery : For Inserting/Deleting/Updating the rows into table

ExecuteNonQuery("DELETE FROM TABLE");
ExecuteNonQuery("UPDATE TABLE SET COLUMNNAME = 'A'");

What is the difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar

ExecuteNonQuery

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 about ExecuteNonQuery)

SqlCommand.ExecuteNonQuery MSDN Documentation

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.(Read More about ExecuteReader)

SqlCommand.ExecuteReader MSDN Documentation

Execute Scalar

Execute Scalar 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 about Execute Scalar)

SqlCommand.ExecuteScalar MSDN Documentation

Use ExecuteScalar when your query returns a single value. If it returns more results, then the end result is the first column of the first row. An example might be SELECT Count(*) from MyTable

Use ExecuteReader for getting result set with multiple rows/columns (e.g., SELECT col1, col2 from MyTable.

Use ExecuteNonQuery for SQL statements which will not retrieve results from database but make updation in existing database (e.g., UPDATE, INSERT, etc.).