为什么在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 totrue
to report only the number of rows actually changed byUPDATE
orINSERT … 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 theCLIENT_FOUND_ROWS
flag tomysql_real_connect()
when connecting to mysqld [ed. note: this is the same asUseAffectedRows
], the affected-rows value is the number of rows "found"; that is, matched by theWHERE
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:
- 在连接字符串中
- 设置
UseAffectedRows = true;
;这可能会导致其他UPDATE
查询的更改. - 向
WHERE
子句添加更多条件,例如WHERE ... AND SELLER_IMAGE_ID!= SellerImageID和SELLER_BUSINESS_LOGO_ID!= BusinessImageID
,以确保仅找到该行并在实际需要更改时进行更新.
- Set
UseAffectedRows=true;
in your connection string; this may cause changes to otherUPDATE
queries. - 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屋!