When to use ExecuteScalar, ExecuteReader, and ExecuteNonQuery?

Question

I am confused with the usage of

1. ExecuteScalar
2. ExecuteReader
3. ExecuteNonQuery

when executing SQL queries in my code. When should I use each of these methods?

Answer 1

• 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 result set 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.

Answer 2

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'");

Answer 3

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

Answer 4

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

Answer 5

Essentially this is simplified, but you can look up each of the SQL terms or .net objects or read about ADO.net on MSDN for more info.

ExecuteScalar when you call an SQL scalar function that just returns a single number.

ExecuteReader when you are making an SQL call that will return a record set from a table, which gives you an SqlDataReader object to retrieve the data in C#.

ExecuteNonQuery is used when there is no return value of any kind expected from SQL server, an example being a simple UPDATE statement.