Dapper(C#)返回匹配行数而非影响行数问题求助

Hey there! I’ve dealt with this exact head-scratcher before—MySQL and Dapper can behave differently than Workbench when it comes to returning actual updated rows vs. just matched rows. Let’s break down the two main fixes you can use:

Option 1: Adjust Your Connection String #

MySQL’s .NET connector has a hidden gem of a parameter that changes how affected rows are calculated. By default, it returns the number of rows that matched your WHERE clause (even if no values were changed). To get the actual number of rows that were modified, add UseAffectedRows=true to your connection string.

For example:

Server=your-server;Database=your-db;Uid=user;Pwd=pass;UseAffectedRows=true;

Once you set this, Dapper’s Execute method will return the correct count—1 on first update, 0 on subsequent calls where no values change.

Option 2: Modify Your Stored Procedure to Return Actual Rows Updated #

If you can’t tweak the connection string (or prefer to handle it at the database level), you can explicitly return the actual updated count from your stored procedure using MySQL’s ROW_COUNT() function.

Here’s how to adjust your procedure:

DELIMITER //
CREATE PROCEDURE UpdateYourTable(IN param1 INT, IN param2 VARCHAR(50))
BEGIN
    UPDATE your_table 
    SET column1 = param2 
    WHERE id = param1;
    
    -- Return the actual number of rows modified
    SELECT ROW_COUNT() AS AffectedRows;
END //
DELIMITER ;

Then, in your C# code with Dapper, instead of using Execute, use QuerySingle<int> to fetch the returned value:

var affectedRows = connection.QuerySingle<int>("UpdateYourTable", 
    new { param1 = 1, param2 = "new value" }, 
    commandType: CommandType.StoredProcedure);

This will give you the real number of rows that were actually updated, not just matched.

Why the Discrepancy Between Workbench and Dapper? #

Workbench (and other MySQL clients) often implicitly use settings that return modified rows by default, but the .NET connector defaults to returning matched rows. That’s why you saw different results—same procedure, different client behaviors under the hood.

内容的提问来源于stack exchange，提问作者afrose