How to find out what was last change to SQL db?

Question

I have a program that is running an UPDATE query. For testing purposes, the query is fixed, just UPDATE table SET field = 'field_name' WHERE other_field = 'id_field_value'. I'm using a MySqlCommand and executing the query with sqlCommand.ExecuteNonQuery(). However, I am running it over and over and it keeps on returning 1 row change each time. Running the same query in MySQL GUI tools returns 0 rows affected, as expected. What's the best way to figure out what changes are occurring to the sql db?

My question has been flagged as a duplicate-I am not asking how to get # rows affected by a sql query. I am asking how to find out what changes were made to a sql table when a query keeps on returning 1 row affected when I expect it to return 0.

NEVERMIND, my question was answered in the comments of the duplicate question (Get affected rows on ExecuteNonQuery). Turns out ExecuteNonQuery() doesn't return the number of rows that were actually modified, just the 'possible' number of rows that could've been modified. This is frankly much less useful.

Possible duplicate of [Get affected rows on ExecuteNonQuery](https://stackoverflow.com/questions/10059158/get-affected-rows-on-executenonquery) — devlin carnate, Jan 30 '18 at 19:14
Issue SELECT field FROM table WHERE other_field = 'id_field_value' — Rogn, Jan 30 '18 at 19:17
You could change the filter on the update to include `AND field <> 'field_name'` basically making sure you don't reset the same value. — juharr, Jan 30 '18 at 19:20

score 0 · Accepted Answer · answered Feb 01 '18 at 05:05

By default, MySqlCommand.ExecuteNonQuery in Connector/NET returns the number of rows that were found, whether they were updated or not (even though the documentation for DbCommand says “For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command”).

To get the behaviour you want, specify UseAffectedRows=True in your connection string (reference), or set MySqlConnectionStringBuilder.UseAffectedRows = true;.

How to find out what was last change to SQL db?

1 Answers1