ansaurus

Question

Benefits of using SqlCommand.ExecuteNonQuery instead of …

Answer 1

+2 A:

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 2010-03-29 19:15:34

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 2010-03-30 18:33:25

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 2010-03-30 18:39:21

Ok, thank you all for your help

AspOnMyNet 2010-03-31 18:45:39

Answer 2

+1 A:

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 Serag 2010-03-29 20:42:26

“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 2010-03-30 18:34:42

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 Serag 2010-03-30 21:54:26

ansaurus

tags:

views:

answers:

Benefits of using SqlCommand.ExecuteNonQuery instead of …

related questions