What does value returned from 'IDbCommand.ExecuteNonQuery' represent?

I am using ADO.net to call stored procedures to perform insertions into a Sybase database, and I want to include unit tests to ensure that the insert calls are working correctly.

According to the spec:

IDbCommand.ExecuteNonQuery()

Returns: The number of rows affected.

As each stored procedure is inserting one row, I am checking that the value returned == 1. However, each procedure returns a different value, ranging from 3 to 8.

So, what exactly does the phrase 'The number of rows affected' mean?

I should sepcify that the开发者_如何学C only statements in the procs are Set, Insert, Print and Return, and the only statement that doesn't seem to affect the return value is Insert!

Thanks.

Then your procedure doing something, it can arise some triggers and system updates, depending on your inner logic. And this number is the sum of this affected rows.

More info: http://www.sqlhacks.com/Retrieve/Row_Count

Without any knowledge of the Sybase provider, a likely answer is that ExecuteNonQuery returns the cumulative sum of 'The number of rows affected' messages 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 on the ExecuteNonQuery return value, but didn't actually verify 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 the stored procedures to control how many count messages is returned. ExecuteNonQuery returns -1 if no messages are returned, by setting SET NOCOUNT ON at the beginning.

SET and RETURN do not send messages to same output stream where count messages go, but PRINT does. Printed strings shouldn't affect ExecuteNonQuery integer return value, but I can't really tell.

The point is that it is better to use T-SQL output parameters to calculate interesting rows than to rely on the ExecuteNonQuery return value. Alternative to output parameters is to use the SELECT resultset and ExecuteScalar

See Also

Overriding rows affected in SQL Server using ExecuteNonQuery?

According to MSDN the return value of 'ExecuteNonQuery' is not relevant for stored procedure execution.

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

You can instead use stored procedure return values or output parameters to obtain the desired value.

Just verify your sql routine logic if its having some dependency logic in case it adds more that 1 rows on certain condition.