c# - MySQL参数必须用C#定义

Question

我在 MySQL 数据库上创建了一个存储过程，如果我直接从 Navicat(一个 SGBD)运行它，它工作正常，但如果我尝试从 C# 代码运行存储过程的脚本，我得到一个错误“Parameter '@必须定义 req'”。我的代码如下:

DROP PROCEDURE IF EXISTS SP_GET_EXPERIENCE_RECORD_STATISTICS;

CREATE PROCEDURE SP_GET_EXPERIENCE_RECORD_STATISTICS(IN startTime  VARCHAR(19), IN endTime VARCHAR(19), IN isEquipment INT)
BEGIN
DECLARE req VARCHAR(1000);
SET @req = "SELECT ";

IF(isEquipment = 1) THEN
    SET @req = CONCAT(@req, "ClientName,");
ELSE
    SET @req = CONCAT(@req, "ContentName 'ClientName',");
END IF;

SET req = CONCAT(req, " COUNT(1) 'ConsumeCount',
SUM(Points) 'PointsCount',
SUM(CASE WHEN Mode = 1 THEN 1 ELSE 0 END) 'CardCount',
SUM(CASE WHEN Mode = 1 THEN Points ELSE 0 END) 'CardPoints',
SUM(CASE WHEN Mode = 0 THEN 1 ELSE 0 END) 'WeChatCount',
SUM(CASE WHEN Mode = 0 THEN Points ELSE 0 END) 'WeChatPoints'
FROM experiencerecord
WHERE EndTime BETWEEN '", startTime, "' AND '", endTime, "'");

IF (isEquipment = 1) THEN
    SET @req = CONCAT(@req, " GROUP BY ClientName");
ELSE
    SET req = CONCAT(req, " GROUP BY ContentName");
END IF;

PREPARE stmt FROM @req;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;

 -- CALL SP_GET_EXPERIENCE_RECORD_STATISTICS('2010-01-01 00:00:00', '2016-12-31 23:59:59', 0);
 -- CALL SP_GET_EXPERIENCE_RECORD_STATISTICS('2010-01-01 00:00:00', '2016-12-31 23:59:59', 1);

还有我的 C# 代码:

MySqlConnection conn = new MySqlConnection(Properties.Settings.Default.DatabaseConnectionString);

public bool mysql(string sql)
    {
        try
        {
            MySqlCommand comm = new MySqlCommand(sql, conn);
            MySqlDataAdapter mda = new MySqlDataAdapter(comm);
            DataSet ds = new DataSet();
            mda.Fill(ds);
            return true;
        }
        catch (Exception ex)
        {
            return false;
            throw ex;
        }

    }

任何想法都将不胜感激。

Answer 1

最后，我将 AllowUserVariables=True; 添加到 connectionString，现在它工作得非常好。