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)
The problem comes when I call this stored procedure from C#
I tried calling this query in a for
loop and each time it returns 1
.
How do I get the rows affected by the query, not the rows found by the query?
Thanks.
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:
server=localhost;user id=test;password=test;database=test;UseAffectedRows=True
I've tried and it works perfectly fine both with ADO.NET and Dapper.
You can use in your stored procedure RETURN @@ROWCOUNT
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;