Investigation: ExecuteNonQuery() always returns -1

Generally, ExecuteNonQuery() is used to return number of rows affected by INSERT/ UPDATE/ DELETE operations by the statement. (A statement can be a plain SQL Query, or it can be a Stored Procedure, etc)

If it returns 0 or more, then it indicates the number of rows affected by INSERT/ UPDATE/ DELETE. (And if any other rows are affected by underlying trigger, then it is also counted in the return value)

But in some cases, it returns -1, even if your INSERT/ UPDATE/ DELETE operation is performed successfully.

The most likely reason for this behavior is the use of "SET NOCOUNT ON" statement in SQL Statement (or stored procedure, etc).

SET NOCOUNT ON turns off row count and thus it does not count affected rows.

The easiest resolution is to remove SET NOCOUNT ON.

But, SET NOCOUNT ON is there to improve performance. If its not used, SQL Server keeps sending messages to client after executing each T-SQL statement. So its recommended to use NOCOUNT ON.

To deal with this issue, you should use @@ROWCOUNT in your stored procedure and assign it to the OUTPUT parameter.

@@ROWCOUNT also returns number of rows affected, in case of SET NOCOUNT ON

So, though your ExecuteNonQuery returns -1, you will have the count of affected rows in the OUTPUT parameter.