Now here in this tutorial, I’ll explain top main difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar in detail with example.
In my previous tutorials, I’d explained simple insert update and delete, gridview inline insert update delete, difference between dataset datareader dataadapter and dataview, and other more cracking tutorials on Difference, Gridivew, Asp.net here.
What is Difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar?
1. What is ExecuteReader?
The SqlCommand ExecuteReader in Ado.net is used to get and read the set of rows fetched using SQL queries or stored procedures from the SQL database table.
ExecuteReader object is a forward-only and supports read-only access of queried results that fetched from the database tables cannot be modified as it always opens in read-only mode.
In short, ExecuteReader is generally used to get or read data from resultset so it’s appropriate with SELECT Command.
2. What is ExecuteNonQuery?
The SqlCommand ExecuteNonQuery in Ado.net returns a number of row(s) affected while performing SQL operations like INSERT, UPDATE, DELETE, etc.
ExecuteNonQuery return -1 if there is no row affected, otherwise return int value indicating a number of rows/columns affected while performing SQL operation.
In short, ExecuteNonQuery is generally used when we want to perform any operation on resultset so it’s appropriate with INSERT, UPDATE, DELETE Command.
3. What is ExecuteScalar?
The SqlCommand ExecuteScalar in Ado.net returns the first column’s first cell value of the first row from the resultset returned by the SQL query or stored procedure, meaning return an only single value and additional columns or rows are ignored.
ExecuteScalar will return null reference if resultset is empty, and can returns a maximum of 2033 characters.
In short, ExecuteScalar is generally used when we need only first value of resultset or aggregate functions like SUM, COUNT, AVG, etc in SELECT Command.
These are the common differences that we need to clear before using them.
Let me know if you’ve any questions or doubts about this tutorial by writing down your queries in comment below. I would be happy to provide you my feedback as soon as possible.