1

a) SqlCommand.ExecuteNonQuery is used for update, insert and delete operations. Besides the fact that by using ExecuteNonQuery instead of ExecuteReader we automatically know there won’t be any query results returned, are there some other benefits/reasons why ExecuteNonQuery should be used?

b) Similarly, if we want a database operation to return a single value, we should use ExecuteScalar instead of ExecuteNonquery ,where with the latter result would be returned via SqlParameter. Is there any particular reason why we should prefer ExecuteScalar over ExecuteNonQuery?

thanx

AspOnMyNet
  • 1,958
  • 4
  • 22
  • 39

2 Answers2

2

For the 2nd point...

ExecuteScalar returns 1st column of 1st row: the entire dataset is passed back to the client. Even if the dataset is exactly one row and exactly one column, it's still less efficient to return a dataset than use an output/return parameter

The same applies to the 1st point too: more efficient to not process a recordset.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • A) “ExecuteScalar returns 1st column of 1st row: the entire dataset is passed back to the client. Even if the dataset is exactly one row and exactly one column, it's still less efficient to return a dataset than use an output/return parameter” So it would be better if I used ExecuteNonQuery instead of ExecuteScalar and retrieve a value via output parameter? B) BTW – through what “channel” does ExecutenonQuery return the number of affected rows – I assume it doesn’t return it as a return value or output parameter?! – AspOnMyNet Mar 30 '10 at 18:33
  • 1
    A. Yes. B. The rowcount is another dataset. Please see my question: http://stackoverflow.com/questions/1483732/set-nocount-on-usage where it was investigated in an answer – gbn Mar 30 '10 at 18:39
1

For the 1st point...

You have said:

we automatically know there won’t be any query results returned

Actually, ExecuteNonQuery returns the number of affected rows, which is very important if your logic after executing the query depends on whether the database has been changed or not.

Sameh Deabes
  • 2,960
  • 25
  • 30
  • “ExecuteNonQuery returns the number of affected rows, which is very important if your logic after executing the query depends on whether the database has been changed or not.” But why would it be less efficient to use ExecuteReader instead of ExecutenonQuery and have a stored procedure return the number of affected rows via an output or return parameter? – AspOnMyNet Mar 30 '10 at 18:34
  • 1
    By "less efficient" you are talking about a few bytes. If you are caring about those few bytes then I think gbn's answer is sufficient. But if you are caring about simplicity, I think using ExecuteScalar is more simple for returning a single value. I don't prefer using a DataReader unless I need to return more than a single value; that is because it needs extra code (for example: to make sure it has been closed properly) – Sameh Deabes Mar 30 '10 at 21:54