为什么在C#代码中MySQL返回始终为1，而在存储过程中进行测试时却不是? [英] Why MySQL return is always 1 in C# code but not when I test in stored procedure?

问题描述

我正在使用MySQL开发ASP.net应用程序，并且有一个与存储过程返回值有关的问题.

I am developing an ASP.net application using MySQL and have a question related to a stored procedure return value.

这是我的存储过程:

CREATE DEFINER=`pcg`@`%` PROCEDURE `UpdatePreSellerProfile`(
IN UserID INT(11),
IN SellerImageID INT(11),
IN BusinessImageID INT(11),
OUT ProfileUpdated INT(1)
)
BEGIN
SET @Approved = 'APPROVED';
UPDATE user SET 
    SELLER_IMAGE_ID = COALESCE((SELECT IMAGE_ID FROM image_url WHERE IMAGE_USER_ID = UserID AND IMAGE_ID=SellerImageID),SELLER_IMAGE_ID),
    SELLER_BUSINESS_LOGO_ID = COALESCE((SELECT IMAGE_ID FROM image_url WHERE IMAGE_USER_ID = UserID AND IMAGE_ID=BusinessImageID),SELLER_BUSINESS_LOGO_ID)
WHERE (USER_LOGIN_ID = UserID AND USER_PROFILE_STATUS = @Approved);
SET ProfileUpdated = ROW_COUNT();
END

当我使用以下MySQL脚本测试此代码时，总是在没有更新的情况下始终为0( @ProfileUpdated ).

When I test this code with following MySQL script I get 0 (@ProfileUpdated) always when there is no update.

call UpdatePreSellerProfile(@UserID, @SellerImageID, @BusinessImageID ,@ProfileUpdated);

但是当我在C#代码中进行检查时，它始终显示1( ProfileUpdated ).

But when I check this in my C# code, it is always showing 1 (ProfileUpdated).

if (oMySQLConnecion.State == System.Data.ConnectionState.Open)
{
    MySqlCommand oCommand = new MySqlCommand("UpdatePreSellerProfile", oMySQLConnecion);
    oCommand.CommandType = System.Data.CommandType.StoredProcedure;

    MySqlParameter sqlProfileUpdated = new MySqlParameter("@ProfileUpdated", MySqlDbType.VarString);
    sqlProfileUpdated.Direction = System.Data.ParameterDirection.Output;
    oCommand.Parameters.Add(sqlProfileUpdated);

    oCommand.Parameters.AddWithValue("@UserID", UserID);
    oCommand.Parameters.AddWithValue("@SellerImageID", oSeller.SellerImageID);
    oCommand.Parameters.AddWithValue("@BusinessImageID", oSeller.BusinessLogoID);

    oCommand.ExecuteNonQuery();
    Int16 ProfileUpdated = Convert.ToInt16(oCommand.Parameters["@ProfileUpdated"].Value);

    if (ProfileUpdated > 0) // <<-- Should be greater only if it is updated is sucessfull
    {
        oDBStatus.Type = DBOperation.SUCCESS;
        oDBStatus.Message.Add(DBMessageType.SUCCESSFULLY_DATA_UPDATED);
    }
    else
    {
        oDBStatus.Type = DBOperation.ERROR;
        oDBStatus.Message.Add(DBMessageType.ERROR_NO_RECORDS_UPDATED);
    }
    oMySQLConnecion.Close();
}

为什么MySQL脚本与C#代码之间有什么区别?

Why is the difference between MySQL script vs C# code?

推荐答案

除非设置了 UseAffectedRows 连接字符串选项，否则默认为 false .这意味着:

Unless you have set the UseAffectedRows connection string option, it defaults to false. This means:

当 false (默认)时，连接将报告找到的行，而不是已更改(受影响)的行.设置为 true 以仅报告通过 UPDATE 或 INSERT…ON DUPLICATE KEY UPDATE 语句实际更改的行数.

When false (default), the connection reports found rows instead of changed (affected) rows. Set to true to report only the number of rows actually changed by UPDATE or INSERT … ON DUPLICATE KEY UPDATE statements.

此外，从文档中 ROW_COUNT 函数:

Additionally, from the documentation of the ROW_COUNT function:

对于 UPDATE 语句，默认情况下受影响的行值为实际更改的行数.连接到mysqld时，如果将 CLIENT_FOUND_ROWS 标志指定为 mysql_real_connect().注意:这与 UseAffectedRows ]相同，受影响的行值为找到"的行数；即与 WHERE 子句匹配.

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 [ed. note: this is the same as UseAffectedRows], the affected-rows value is the number of rows "found"; that is, matched by the WHERE clause.

因此，存储过程中的 UPDATE用户语句将返回查询找到的行数，而不是实际更新的行数.

Thus, the UPDATE user statement in your stored procedure will return the number of rows that were found by the query, not the number that were actually updated.

要解决此问题，请执行以下任一操作:

To fix this, either:

在连接字符串中
1. 设置 UseAffectedRows = true; ；这可能会导致其他 UPDATE 查询的更改.
2. 向 WHERE 子句添加更多条件，例如 WHERE ... AND SELLER_IMAGE_ID！= SellerImageID和SELLER_BUSINESS_LOGO_ID！= BusinessImageID ，以确保仅找到该行并在实际需要更改时进行更新.

1. Set UseAffectedRows=true; in your connection string; this may cause changes to other UPDATE queries.
2. Add more conditions to the WHERE clause, e.g., WHERE ... AND SELLER_IMAGE_ID != SellerImageID AND SELLER_BUSINESS_LOGO_ID != BusinessImageID, to make sure the row is only found and updated if it actually needs to change.

这篇关于为什么在C#代码中MySQL返回始终为1，而在存储过程中进行测试时却不是?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！