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.
Quick Note: ExecuteReader returns a DataReader object, So use it when a resultset is expected even if it’s an empty resultset, It does not matter. I explained more about ExecuteReader here with examples and sample code.
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.
Quick Note: ExecuteNonQuery does not return any resultset and it is more appropriate and efficient when we just want to execute SQL query statements (like INSERT, UPDATE, DELETE) or stored procedures and doesn’t expecting resultset in return from those queries. I explained more about ExecuteNonQuery here with example and sample code.
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.
Quick Note: ExecuteScalar returns a single value meaning one column with a single row. If you are reusing a statement that returns a resultset with no of row(s) but all you need is the topmost, leftmost value. ExecuteScalar returns an Object, which can be assigned to a variable (be careful when casting to a variable). I explained more about ExecuteScalar here with examples and sample code.
These are the common differences that we need to clear before using them. If you have any queries please leave a comment below, I’ll try my best to reply to you soon.
fine article