ExecuteNonQuery returns 1, even if the update instruction did not affect any row

Question

I ran into a rather strange problem.

My DAL was written using OdbcConnection objects and worked fine.

However, I had to comply with some requirements and therefore had to move the system to use MySqlConnection

Do not worry if you say so.

However, a slight misunderstanding now arises: when I execute the UPDATE command without entering any new details (say, I change the user "test" username to "test"), command.ExecuteNonQuery() returns 1 anyway.

With the previous system and OdbcCommand objects OdbcCommand it returned 0 if the field has not changed.

Is this just the basic difference between the two systems, or is there something I missed here?

Just code, even if it is very simple:

 private readonly string _updateUserCommand = "UPDATE user u " + "JOIN city c ON c.Name=?City " + "SET `City Id`=c.Id, u.Username=?Username WHERE u.Id=?Id"; // (...) MySqlCommand command = null; try { connection.Open(); //First step: storing the user in table user //Creating the actual command: command = new MySqlCommand(_updateUserCommand, connection); command.Parameters.AddWithValue("?City", u.City); command.Parameters.AddWithValue("?Username", u.Name); command.Parameters.AddWithValue("?Id", u.Id); int i = command.ExecuteNonQuery(); if (i != 0) return true; else return false; } 

Answer 1

Your explanation does not make much sense. If you give a valid identifier and update the username, even if you upgrade to the same name, you can expect 1 line to be affected. i.e. there is one line with userId

Answer 2

ExecuteNonQuery returns the number of rows that it has changed, even if the change is not noticeable to the reader. The request found a match and it updated one row. SQL really does not pay so much attention to what was the old value, it just overwrites it and thinks that with a single line change.