Mysql stored procedure with two OUT parameters and c#

Question

I have a MySQL stored procedure with two OUT parameters as below.

CREATE `GetCourses`(out UG varchar(20),out PG varchar(20))
BEGIN
 SELECT course_name into UG FROM test_db.courses where group_id=1;
 select course_name into PG from test_db.courses where group_id=2;
END

Now in windows Form, I have two Comboboxes where first combo box should be bind with OUT variable UG and the other combobox should be bind with another OUT variable PG.

How to achieve this using c#?

Thanks in advance.

For those interested, I showed a MySQL / c# Visual Studio 2015 working example [HERE](http://stackoverflow.com/a/38706288). That situation was one of IN and `OUT` parameters. The focus naturally was on the `OUT`. — Drew, Aug 01 '16 at 19:31

score 5 · Answer 1 · answered Aug 26 '15 at 17:22

It will be something like this....

//Basic command and connection initialization 
MySqlConnection conn = new MySqlConnection(ConnectString);
MySqlCommand cmd = new MySqlCommand("GetCourses", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;


// Add parameters
cmd.Parameters.Add(new MySqlParameter("?UG", MySqlDbType.VarChar));
cmd.Parameters["?UG"].Direction = ParameterDirection.Output;

cmd.Parameters.Add(new MySqlParameter("?PG", MySqlDbType.VarChar));
cmd.Parameters["?PG"].Direction = ParameterDirection.Output;

// Open connection and Execute 
conn.Open();
cmd.ExecuteNonQuery();

// Get values from the output params
string PG = (string)cmd.Parameters["?PG"].Value;
string UG = (string)cmd.Parameters["?UG"].Value;

Thanks for the suggestion. But the OUT parameters will have set of values. For eg, UG will have the values 'BCOM,BSC,BED'.. I want these to be bind with a combobox.. could you suggest.. — ARK, Aug 27 '15 at 04:55

score 1 · Answer 2 · edited Aug 14 '17 at 17:59

DELIMITER $$
CREATE PROCEDURE `surveydb`.`getSurveyWithNameAndAddress` (IN surveyId INT, IN phoneNumber VARCHAR(255),
    OUT surName VARCHAR(255), OUT surAddress VARCHAR(255))
BEGIN
    SELECT su.`name`,su.`address` INTO surName,surAddress  FROM survey AS su 
    WHERE su.`survey_id`=surveyId AND su.`phone`=phoneNumber;

END

if you want drop procedure

DROP PROCEDURE getSurveyWithNameAndAddress

call above procedure like below

CALL getSurveyWithNameAndAddress(2,9888710807, @name, @surAddress);

SELECT @surAddress AS adr, @name AS na

Mysql stored procedure with two OUT parameters and c#

2 Answers2