What value should .net SqlCommand.ExecuteNonQuery() return if no rows are affected?

Question

I have the following code:

int result = -1;
StringBuilder sb = new StringBuilder();
SqlCommand cmd = MyConnection.
sb.AppendLine("delete from Table1 where ID in");
sb.AppendLine("(select id from Table1 t1 where  not exists(select * from Table2 t2 where t2.Table1ID = t1.ID))");
cmd.CommandText = sb.ToString();
result = cmd.ExecuteNonQuery();
_log.Info("StoredXMLDocument Records Deleted: " + result.ToString());

That SQL, in a more readable format, is:

delete from Table1 where ID in
(select id from Table1 t1 where  not exists(select * from Table2 t2 where t2.Table1ID = t1.ID))

I know that the SQL, when executed directly in the database, deletes no rows. When this code runs, however, result gets a value of 1. I was expecting it to be 0. There are no triggers on Table1. Am I missing something? Why is it 1?

Addendum: ok, now when I run the sql in the database, it deletes a row. Note that before all tests, I restore the database from a backup I saved just for this purpose. I swear it wasn't deleting any rows this morning or yesterday afternoon, and yes that was after restoring the backup. I'm going to let me crazy pills wear off, and revisit the issue this afternoon. With a hopefully clearer mind.

Answer 1

It should return 0.

Try comparing SELECT COUNT(*) Table1 before and after the DELETE.

Answer 2

As a side note, your query can be simplified to:

delete from Table1 where ID not in (select Table1ID from Table2)

Try this one for interest's sake:

delete from Table1 where 1 = 0 --we know this will delete no rows

Also, please confirm whether there are any triggers on Table1.