如何使用mysql使用输入和输出参数在Entity Framework Core中调用存储过程 [英] How to call stored procedure in Entity Framework Core with input and output parameters using mysql
问题描述
我将ASP.net Core 2.2与Entity Framework core 2.2.6和Pomelo.EntityFrameworkCore.MySql 2.2.0用于与MySQL的连接,我有一个存储过程,该过程需要3个输入参数和1个输出参数。我可以在MySQL工作台中调用它,例如
I am using ASP.net Core 2.2 with Entity Framework core 2.2.6 and Pomelo.EntityFrameworkCore.MySql 2.2.0 for connectivity with MySQL, I have a stored procedure which takes 3 input parameters and 1 output parameter. I am able to call it in MySQL workbench like
CALL GetTechniciansByTrade('Automobile', 1, 10, @total);
select @total;
现在我想使用实体框架核心来称呼它,我当前使用的代码是
Now I want to call this using entity framework core, the code I am currently using is
var outputParameter = new MySqlParameter("@PageCount", MySqlDbType.Int32);
outputParameter.Direction = System.Data.ParameterDirection.Output;
var results = await _context.GetTechnicians.FromSql("Call GetTechniciansByTrade(@MyTrade, @PageIndex, @PageSize, @PageCount OUT)",
new MySqlParameter("@MyTrade", Trade),
new MySqlParameter("@PageIndex", PageIndex),
new MySqlParameter("@PageSize", PageSize),
outputParameter).ToListAsync();
int PageCount = (int)outputParameter.Value;
我当前遇到的异常是
当CommandType为文本(参数名称:@PageCount)时仅支持ParameterDirection.Input
Only ParameterDirection.Input is supported when CommandType is Text (parameter name: @PageCount)
推荐答案
我根据此问题,使用@ matt-g建议找到了解决方案。
为此,我必须使用ADO.net。
I found the solution using @matt-g suggestion based on this Question. I had to use ADO.net for this as
var technicians = new List<TechnicianModel>();
using (MySqlConnection lconn = new MySqlConnection(_context.Database.GetDbConnection().ConnectionString))
{
lconn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = lconn;
cmd.CommandText = "GetTechniciansByTrade"; // The name of the Stored Proc
cmd.CommandType = CommandType.StoredProcedure; // It is a Stored Proc
cmd.Parameters.AddWithValue("@Trade", Trade);
cmd.Parameters.AddWithValue("@PageIndex", PageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.AddWithValue("@PageCount", MySqlDbType.Int32);
cmd.Parameters["@PageCount"].Direction = ParameterDirection.Output; // from System.Data
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
technicians.Add(new TechnicianModel()
{
City = reader["City"].ToString(),
ExperienceYears = reader["ExperienceYears"] != null ? Convert.ToInt32(reader["ExperienceYears"]) : 0,
Id = Guid.Parse(reader["Id"].ToString()),
Name = reader["Name"].ToString(),
Qualification = reader["Qualification"].ToString(),
Town = reader["Town"].ToString()
});
}
}
Object obj = cmd.Parameters["@PageCount"].Value;
var lParam = (Int32)obj; // more useful datatype
}
}
这篇关于如何使用mysql使用输入和输出参数在Entity Framework Core中调用存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!