What does the value returned from "IDbCommand.ExecuteNonQuery" mean?

Question

I use ADO.net to call stored procedures to perform insertions into the Sybase database, and I want to include unit tests to ensure that insert calls work correctly.

According to specification:

IDbCommand.ExecuteNonQuery() 

Returns: the number of rows affected.

Since each stored procedure inserts one row, I verify that the returned value == 1. However, each procedure returns a different value in the range of 3 to 8.

So what does the phrase “Number of rows affected” mean?

I must emphasize that the only statements in procs are Set, Insert, Print, and Return, and the only statement that does not seem to affect the return value is Insert!

Thanks.

Answer 1

According to MSDN, the return value of 'ExecuteNonQuery' is not related to the execution of the stored procedure.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of operators, the return value is -1.

Instead, you can use the return values ​​of the stored procedure or the output parameters to get the desired value.

Answer 2

Then your procedure does something, it can cause some triggers and system updates, depending on your internal logic. And this number is the sum of these affected lines.

Further information: http://www.sqlhacks.com/Retrieve/Row_Count

Answer 3

Just check your sql subsystem logic if it has some dependency logic if it adds more than 1 row under certain conditions.

Answer 4

Without any knowledge of the Sybase provider, the likely answer is that ExecuteNonQuery returns the sum of the "Number of rows affected" returned by the SQL statement. In SQL Server, @@ ROWCOUNT returns the number of rows affected by the last statement.

I found this comment in the SqlCommand.cs source in the return value of ExecuteNonQuery, but did not actually check it:

 // sql reader will pull this value out for each NextResult call. It is not cumulative // _rowsAffected is cumulative for ExecuteNonQuery across all rpc batches internal int _rowsAffected = -1; // rows affected by the command 

In SQL Server, you can use SET NOCOUNT in stored procedures to control the number of count messages returned. ExecuteNonQuery returns -1 if messages are not returned by setting SET NOCOUNT ON at the beginning.

SET and RETURN do not send messages to the same output stream as list messages, but PRINT. The printed lines should not affect the return value of the integer value of ExecuteNonQuery, but I cannot say.

The point is, it's better to use T-SQL output parameters to compute interesting strings than relying on the return value of ExecuteNonQuery. An alternative to the output parameters is to use the SELECT and ExecuteScalar result set.

see also

Overriding rows affected in SQL Server using ExecuteNonQuery?