Benefits of using SqlCommand.ExecuteNonQuery instead of …

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 ExecuteNo开发者_如何学运维nquery ,where with the latter result would be returned via SqlParameter. Is there any particular reason why we should prefer ExecuteScalar over ExecuteNonQuery?

thanx

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.

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.