CODEWITHSUNDEEP
c#mysqlparameters
Hans
Hans

Reputation: 382

MySQL Parameter must be defined with C#

I created a stored procedure on a MySQL database and it works fine if i run it directly from Navicat (a SGBD) but if i try to run the script of the stored procedure from C# code i get an error "Parameter '@req' must be defined". My code bellow:

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);

And my code in 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;
        }

    }

Any idea where it come from will be very appreciated.

Upvotes: 3

Views: 3211

Answers (2)

Hans
Hans

Reputation: 382

Finally i added AllowUserVariables=True; to the connectionString and now it works super fine.

Upvotes: 9

Ali Rasheed
Ali Rasheed

Reputation: 2817

Seems a typo error

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

Moreover I don't see any AddWithParameter line in your C# code that should be there as @req is a parameter.

MySqlDataAdapter mda = new MySqlDataAdapter(comm);
mda.Parameters.AddWithValue("@req",value_goes_here);

Upvotes: 0

Related Questions