C# SqlCommand.ExecuteNonQuery returning -1 for successful UPDATE SQL statement

Viewed 104

The following code, using .ExecuteNonQuery, appears to work:

public static void StringQueryWithParameterList(
    string queryString,
    List<string> keys,
    List<string> values
)
{
    SqlCommand command = new SqlCommand(queryString);
    int affected;

    command.Connection = GetConnection();
    command.CommandType = CommandType.Text;
    for (int i = 0; i < keys.Count; i++) {
        command.Parameters.AddWithValue(keys[i], values[i]);
    }
    affected = command.ExecuteNonQuery();  // Note: this is returning -1 ... why?

    if (affected < 1)
    {
        throw new Exception("No rows were updated by this update query.");
    }
}

.ExecuteNonQuery is returning -1 for a reason I don't understand. I have observed that this function does work, the database is being updated as expected when I check in SQL Server Management Studio. Obviously I want to use the return value I'm expecting from .ExecuteNonQuery to verify that a row was successfully changed, but this is not working.

I investigated the MSDN docs for .ExecuteNonQuery (here). The docs report that a -1 result is expected for the following scenarios:

  • For all SQL statement types other than UPDATE, INSERT, and DELETE (but mine is an UPDATE)
  • If no statements are detected that contribute to the count (but I'm seeing the database is updated as expected, so this doesn't make sense to me)
  • If a rollback occurs (obviously not, as a I see my changes in the database)

For transparency's sake, here is the query I'm using:

UPDATE my_table SET my_col = 2 WHERE my_id = 'some_id';
1 Answers

Probably SET NOCOUNT is ON at instance level.

To check it you can use DBCC USEROPTIONS;

In order to SET NOCOUNT OFF, you can

sp_configure 'user options' 0

PS: AddWithValue(keys[i], values[i]) is not type safe. I don't advise using it.

Related