SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete
Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.
Why is that?
Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.
When checking the documentation we can see that there are some conditions that return -1.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.
When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of
rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
".NET Framework Class Library - SqlCommand.ExecuteNonQuery Method"
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx
Now, sometimes all you do is an INSERT but that still returns -1, how come?
I would say the most common reason is that the INSERT is done via stored procedures and that the SET NOCOUNT options is set to ON.
What this option does is the following:
Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
"SQL Server 2008 Books Online (December 2008) - SET NOCOUNT (Transact-SQL)"
https://msdn.microsoft.com/en-us/library/ms189837.aspx
So basically, if the SQL does not return the number of affected rows, how could the SqlCommand.ExecuteNonQuery know? Well, it doesn’t and you’ll have your -1.
Being a fan of exemplifying everything, I’ll do it here as well.
Startup SSMS and create a table and a stored procedure in SQL Server, like so:
create table ENQ (id int identity, aName nvarchar(20))
insert into ENQ values ('John')
select * from ENQ
go
-- create procedure for insert
create procedure InsertToENQ (@pNewName nvarchar(20)) as
set nocount on
insert into ENQ values (@pNewName)
return
go
-- Insert a name vis stored proce to see that all works as expected.
exec InsertToENQ 'Peter'
select * from ENQ
-- drop table ENQ
-- drop procedure InsertToENQ
You know have to rows in the database, John and Peter.
Create a new C# console application in Visual Studio and replace the Main method with this:
static void Main(string[] args)
{
String cs = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";
try
{
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
// Using sql
SqlCommand cmd = new SqlCommand("insert into ENQ values ('Chuck')", con);
int retVal = cmd.ExecuteNonQuery();
Console.WriteLine("{0, -2} returned by {1}", retVal, cmd.CommandText);
// Using sp
cmd.CommandText = "InsertToENQ";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@pNewName", "Robert");
retVal = cmd.ExecuteNonQuery();
Console.WriteLine("{0, -2} returned by {1}\n", retVal, cmd.CommandText);
// Print rows to verify that both cmd's actually inserted the rows.
cmd.CommandText = "select aName from ENQ";
cmd.CommandType = System.Data.CommandType.Text;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("{0}", rdr[0].ToString());
}
con.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
And run it. The output should be:
1 returned by insert into ENQ values ('Chuck')
-1 returned by InsertToENQ
John
Peter
Chuck
Robert
So here we can see that the two added rows are there (Chuck, Robert) but the stored procedure call returned -1 for the number of affected rows because the SET NOCOUNT option is set to ON.
You can set the SET NOCOUNT directly in the command text, then it makes more sense why it does not return any affected rows:
This will return -1 as well. But it is more obvious here, if you disable row counting, how could you count the rows….
SqlCommand cmd = new SqlCommand("set nocount on; insert into ENQ values ('Chuck')", con);
int retVal = cmd.ExecuteNonQuery();
I believe that some of the confusion comes from stored procedures templates being generated differently depending on from where you create them.
If you create a new stored procedure from Visual Studio, the SET NOCOUNT is OFF, however, if you create it from SQL Server Management Studio, it is set to ON.
VISUAL STUDIO DEFAULT:
CREATE PROCEDURE dbo.StoredProcedure2
/*(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)*/
AS
/* SET NOCOUNT ON */
RETURN
SSMS DEFAULT:
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
HTH