dapper c# returns rows found not rows affected

Question

dapper c# returns rows found not rows affected

c# dapper mysql

Question

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.

afrose

Popular Answer

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;

rnest

View more on Stack Overflow

CodeFuller · Accepted Answer · 2/10/2018 2:17:19 PM

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.

dapper c# returns rows found not rows affected

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator