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

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 wea开发者_开发知识库r off, and revisit the issue this afternoon. With a hopefully clearer mind.

It should return 0.

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

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.