C #: ExecuteNonQuery () returns -1

Question

I have used this method before to return the number of rows. I run the insert method, the insert works fine in the stored procedure, but the return value from ExecuteNonQuery always returns -1.

Here is my C # code:

int ret = 0; using (SqlConnection conn = new SqlConnection(this.ConnectionString)) { using (SqlCommand cmd = new SqlCommand(QueryName, conn)) { conn.Open(); if (Params != null) cmd.Parameters.AddRange(Params); cmd.CommandType = CommandType.StoredProcedure; ret = cmd.ExecuteNonQuery(); conn.Close(); } } return ret; 

Why am I getting -1 instead of the actual number of rows?

Answer 1

If you use this method to call a storage procedure that performs an UPDATE / INSERT on a table, the method returns -1 if the stored procudere has SET NOCOUNT when it is turned on.

- source

Answer 2

From Microsoft:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists in an inserted or updated table, the return value includes the number of rows affected by both the insert and update operations, as well as the number of rows affected by the trigger or triggers. For all other types of operators, the return value is -1. If a rollback occurs, the return value is -1.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

You probably get -1 because you are using a stored procedure, not a direct table replacement.

Answer 3

What value are you returning from the stored procedure? Can you show the code for the stored procedure. You probably need to edit the stored procedure to return the desired value, OR, your stored procedure returns -1 either specifically or the variable returned by the stored procedure, whose value is -1. A.

Answer 4

Get the output of the row counter from the stored procedure, for example, using @rowCount=@@Rowcount . In DAL use dbManager.AddOutParameter("RowCount", DbType.Int32, rowCount); Then in the final lines you will get as (int)dbManager.GetParameterValue("RowCount") .