I have a simple stored procedure which does update on a table.
CREATE PROCEDURE `test_v1`( out v_changed_rows int ) BEGIN update mytable set color = 'green' where id = 964291; set v_changed_rows= ROW_COUNT(); END
Calling this stored procedure from mysql workbench gives correct rows affected(i.e. first time it will return 1 and hence forth returns 0 because I'm updating it with the same value and hence no change)
I tried calling this query in a
for loop and each time it returns
How do I get the rows affected by the query, not the rows found by the query?
There is such note for ROW_COUNT() function:
For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows â€œfoundâ€; that is, matched by the WHERE clause.
Although it says that number of actually changed rows is returned by default, for MySql .Net client the default behavior is to return number of rows matched by WHERE clause. This should be due to the value of
client_flag parameter passed to mysql_real_connect.
You could change this behavior by explicitly setting UseAffectedRows to
True in your connection string:
I've tried and it works perfectly fine both with ADO.NET and Dapper.
You can use in your stored procedure
And inside your method do smth like that:
var dynamicParameters = new DynamicParameters(); dynamicParameters.Add("@UpdatedCounter", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); var result = await _connection.ExecuteAsync("sp_Update", dynamicParameters, commandType: CommandType.StoredProcedure); int updatedCounter = dynamicParameters.Get<int>("@UpdatedCounter"); return updatedCounter;