How to pass the output parameter for MySQL stored procedure for .net core using Pomelo ORM

Question

Here is my SP in MySQL

CREATE PROCEDURE `GetMemberID`(
   IN uname     VARCHAR(128),
   OUT MemberID     INT
)
BEGIN  
   SELECT ID INTO MemberID FROM `Member` Where username = uname;    
END

And I am calling SP from .netcore web api project that is using Pomelo ORM.

var usernameParam = new MySqlParameter
{ 
   ParameterName = $"@uname",
   DbType = DbType.String,
   Direction = ParameterDirection.Input,
   Value = "test"
 };
var memberIDParam = new MySqlParameter
{ 
   ParameterName = $"@MemberID",
   DbType = DbType.Int32,
   Direction = ParameterDirection.Output
 };

rContext.Database.ExecuteSqlRaw($"Call GetMemberID (@uname=@uname, @MemberID=@MemberID OUT)"
            , usernameParam , memberIDParam);

I am getting the following error when used output parameter.

Only ParameterDirection.Input is supported when CommandType is Text (parameter name: @MemberID)

How can I pass output parameter?

score 2 · Accepted Answer · answered Feb 21 '20 at 09:15

It's a known issue in github ,as mguinness commented

RelationalCommand.csdon't specify CommandType so it'll default to CommandType.Text which means output parameters won't work.

Here is a workaround ,you could refer to

 using (MySqlConnection lconn = new MySqlConnection(Configuration.GetConnectionString("DefaultConnection")))                
            {
                lconn.Open();
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.Connection = lconn;
                    cmd.CommandText = "GetMemberID"; // The name of the Stored Proc
                    cmd.CommandType = CommandType.StoredProcedure; // It is a Stored Proc

                    cmd.Parameters.AddWithValue("@uname", "sherry");
                    cmd.Parameters.Add("@MemberID", MySqlDbType.Int32);
                    cmd.Parameters["@MemberID"].Direction = ParameterDirection.Output; // from System.Data
                    cmd.ExecuteNonQuery();
                    Object obj = cmd.Parameters["@MemberID"].Value;
                    var lParam = (Int32)obj;    // more useful datatype

                }
            }

Reference :How to call stored procedure in Entity Framework Core with input and output parameters using mysql

How to pass the output parameter for MySQL stored procedure for .net core using Pomelo ORM

1 Answers1