Damascus Damascus - 20 days ago 5
C# Question

ExecuteNonQuery returns 1 even if update statement didn't affect any row

I am facing a quite strange problem here.

My DAL was written using

OdbcConnection
objects and was perfectly working.

However I had to respect some requirements and therefore had to move the system to use
MySqlConnection


Shouldn't give any problem, would you say.

However, there is a little misunderstanding now: when I execute an
UPDATE
command, without entering any new detail (let's say I change the user "test"'s username to... "test"), the
command.ExecuteNonQuery()
returns 1 anyway.

With the previous system &
OdbcCommand
objects, it returned 0 if no field changed.

Is it just a basic difference between the two systems or is there anything I've missed here?

Just some code even if it is very basic:

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

Your explanation doesn't make much sense. If you give a valid id and do an update on the username even if you update to the same name you can expect that 1 row will be affected. i.e. there is one row with the userId