带有两个OUT参数的Mysql存储过程和c#

Question

我有一个MySQL存储过程，有两个OUT参数，如下所示。

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

现在在windows窗体中，我有两个组合框，其中第一个组合框应该与OUT变量UG绑定，另一个组合框应该与另一个OUT变量PG绑定。

如何使用c#实现这一点?

Answer 1

就像这样....

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

Answer 2

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

---

删除程序

删除程序getSurveyWithNameAndAddress

像下面那样调用上面的过程

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

SELECT @surAddress AS adr， @name AS na