Dapper 调用mysql 存储过程示例代码

一个简单的示例，我们使用的是mysql 8.0 ,首先创建一个存储过程如下：

CREATE PROCEDURE GetAircraftByModel @Model NVARCHAR(255) AS
BEGIN
    SELECT 
       Id
      ,Manufacturer
      ,Model
      ,RegistrationNumber
      ,FirstClassCapacity
      ,RegularClassCapacity
      ,CrewCapacity
      ,ManufactureDate
      ,NumberOfEngines
      ,EmptyWeight
      ,MaxTakeoffWeight
    FROM Aircraft a
    WHERE a.Model = @Model
END

最终在后台程序中如何使用它呢？当然是在Dapper框架下。我们可以这样调用：

[HttpGet]
public async Task<IEnumerable<Aircraft>> Get(string model)
{
    IEnumerable<Aircraft> aircraft;

    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();

        aircraft = await connection.QueryAsync<Aircraft>("GetAircraftByModel",
                        new {Model = model}, 
                        commandType: CommandType.StoredProcedure);
    }
    return aircraft;
}

最后给一点小提示：在最版本的8.0.27 中也就 8.0.20以后的版本中如果你的存储过程中使用了游标，那么它可能不会被正确的调用。这个暂时宝库为Dapper的Bug，后期可能会解决。

DELIMITER //
CREATE PROCEDURE `SP_WxUserRole`(IN OpenId VARCHAR(30))
BEGIN
    DECLARE  var_role INT DEFAULT 0;
	DECLARE  var_done INT DEFAULT 0;
	DECLARE  var_hasrole INT;
	DECLARE  cursor_roles CURSOR FOR SELECT `RoleID` FROM `Sys_WxUserRole` WHERE `Status`>-1 AND `OpenId`=OpenId;
	DECLARE  CONTINUE HANDLER FOR NOT FOUND SET var_done=1;
	
	OPEN cursor_roles;
	posLoop:LOOP
	IF var_done=1 THEN  LEAVE posLoop; END IF; 
	FETCH  cursor_roles INTO var_hasrole;
	SET var_role = var_role|var_hasrole;
	END LOOP posLoop;
	
	CLOSE cursor_roles;
		
	SELECT var_role;
	
END//