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';