How to get number of rows affected by ExecuteNonQuery and ignore rows of triggers?

Question

I am using ExecuteNonQuery to run an insert proc, it returns 2, but in actual I am inserting only 1 record. I am getting 1 extra due to trigger. Is there anyway that I get only actual number of rows affected. I do not want the rows affected by trigger.

Courthouse

possible duplicate of ExecuteNonQuery() Returns unexpected number of rows affected C# – Aeschylus

You do realize you are complaining about the fact that ExecuteNonQuery returns the correct number, right? – Electroplate

ExecuteNonQurey return number of row affected. It doesn't return inserted rows. #2974654 – Zealous

try dividing the result by 2 :) – Carrageen

Answer 1

If you don't have it already, disable counting rows in your trigger:

SET NOCOUNT ON   

For example:

CREATE TRIGGER [dbo].[triggerName] ON [dbo].[TableName]
AFTER INSERT
AS
SET NOCOUNT ON;
......

MSDN

Reference

Tim i want to clear one thing SET NOCOUNT ON will not return any count right ?? – Baileybailie

@tinka: if the trigger has SET NOCOUNT ON it's affected rows are not counted. But if you insert three rows ExecuteNoQuery still returns 3. – Reference

I am creating my own query builder. It can run any type of query and in my database there are thousands of table and many triggers. So do it need to change all triggers to ignore count? – Courthouse

@girishgupta211: that would be a good approach. It is recommended to SET NOCOUNT ON in triggers, it also increases the performance (as also Reza has mentioned). Otherwise it's not possible to ignore trigger's affected row-count. Of course you could change your query to somehwhat like this: insert into dbo.TableName Values('B'); select @@ROWCOUNT"; and use (int)ExecuteScalar. But then it could be incorrect because it only returns the very last statement's row-count. This would ignore trigger. – Reference

Thanks Tim Schmelter. Let me try this approach. – Courthouse

Answer 2

please refer below mention Image

However, the MSDN article on this function states: 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. If a rollback occurs, the return value is also -1.

See... enter link description here

Andraandrade

How does this answer help to avoid that the rows which were affected by a trigger will not be counted? – Reference