C＃：如何使用Entity Framework Core将记录检索到存储过程？

I have created a new ASP.NET Core 2.1 web application with Angular

我用Angular创建了一个新的ASP.NET Core 2.1 Web应用程序

Visual Studio creates a project that uses the Entity Framework Core with ASP.NET Core MVC.

Visual Studio创建一个使用Entity Framework Core和ASP.NET Core MVC的项目。

I have this problem: I have to read records from a stored procedure:

我有这个问题:我必须从存储过程中读取记录:

CREATE PROCEDURE GetEmployees
    (@PageIndex INT,
     @PageSize INT)
AS
BEGIN
    SELECT * 
    FROM employee 
    ORDER BY id 
        OFFSET @PageSize * (@PageIndex - 1) ROWS 
        FETCH NEXT @PageSize ROWS ONLY;

    SELECT COUNT(*) AS totalCount 
    FROM employee;
END

I found a question that almost solves my question, but unfortunately there is still something that does not work.

我发现了一个几乎可以解决我问题的问题,但不幸的是仍有一些问题无法解决。

This is my code:

这是我的代码:

namespace Angular.Controllers
{
    //[Produces("application/json")]
    [Route("api/Employees")]
    public class EmployeesController : Controller
    {
        private readonly ApplicationDbContext _context;

        public EmployeesController(ApplicationDbContext context)
        {
            _context = context;
        }

        // GET: api/Employees/pageIndex/1/pageSize/1
        [HttpGet("pageIndex/{pageIndex}/pageSize/{pageSize}")]
        public Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
        {
            SqlParameter pageIndexParam = new SqlParameter("@PageIndex", SqlDbType.Int);
            pageIndexParam.Value = pageIndex;

            SqlParameter pageSizeParam = new SqlParameter("@pageSize", SqlDbType.Int);
            pageSizeParam.Value = pageSize;

            // SqlParameter pageIndexParam = new SqlParameter("@PageIndex", pageIndex);
            // SqlParameter pageSizeParam = new SqlParameter("@pageSize", pageSize);

            var cmd = _context.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = "GetEmployees"; // The name of the stored procedure
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            // the 2 parameters to be passed to the procedure
            var param = cmd.CreateParameter();
            param.ParameterName = "@PageIndex";
            param.Value = pageIndexParam;
            cmd.Parameters.Add(param);

            var param2 = cmd.CreateParameter();
            param2.ParameterName = "@pageSize";
            param2.Value = pageSizeParam;
            cmd.Parameters.Add(param2);

            try
            {
                // connection.Open();
                // _context.Database.GetDbConnection().Open(); // it crashes after this line
                _context.Database.OpenConnection(); // it crashes after this line
                var dr = cmd.ExecuteReader(); // ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.
                List<Employee> listEmp = new List<Employee>();

                while (dr.Read())
                {
                    // Is there a faster way to read the whole record?
                    // Or should I write a line for each field?
                    Employee emp = new Employee();
                    emp.ID = System.Int32.Parse(dr["id"].ToString());
                    emp.Fname = dr["FName"].ToString();
                    emp.email = dr["email"].ToString();
                    emp.Lname = dr["LName"].ToString();
                    listEmp.Add(emp);

                    dr.NextResult();
                }

                return Ok(listEmp);
            }
            catch (Exception ex)
            {
                // how can I return an error in json format?
                throw;
            }
        }
    }
}

The problem is in the line where the script ExecuteReader:

问题出在脚本ExecuteReader的行中:

ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.

ArgumentException:从对象类型System.Data.SqlClient.SqlParameter到已知的托管提供程序本机类型不存在映射。

I use Microsoft SQL Server In the Startup.cs file, I configured the connection in this way:

我使用Microsoft SQL Server在Startup.cs文件中,我以这种方式配置连接:

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer("Server=localhost\\SQLEXPRESS;Database=master;Trusted_Connection=True;"));

Can you help me?

你能帮助我吗?

Maybe I managed to pass the parameters correctly (see my solution, in the answer) but I can not extract the records

也许我设法正确传递参数(请参阅我的解决方案,在答案中),但我无法提取记录

3 个解决方案

#1

I am using Entity Framework Core in my current project and using following approach to get data through stored procedure.

我在当前项目中使用Entity Framework Core并使用以下方法通过存储过程获取数据。

First of all Create a Model with Same number and name of properties in your Stored procedure.

首先在存储过程中创建一个具有相同编号和属性名称的模型。

lets assume you are only selecting EmployeeName ,EmployeeId through your stored procedure.

假设您只是通过存储过程选择EmployeeName,EmployeeId。

Model.

 public class EmployeeModel
  {
    public string EmployeeName { get; set; }
    public int EmployeeId{ get; set; }
  }

Go to your Db Context file and register your custom model.

转到您的Db上下文文件并注册您的自定义模型。

 public partial class ApplicationDbContext: DbContext
 {
 public virtual DbSet<EmployeeModel> EmployeeModel{ get; set; }
 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EmployeeModel>(entity =>
        { 
        });
        }
    }

Your Controller

 [HttpGet("pageIndex/{pageIndex}/pageSize/{pageSize}")]
    public Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
    {
    List<EmployeeModel> employeeList = new List<EmployeeModel>();
    employeeList = _context.EmployeeModel.FromSql("GetEmployees 
    @PageIndex,@PageSize", 
    new SqlParameter("@PageIndex", pageIndex),
    new SqlParameter("@PageSize", pageSize)).ToList();
    return Ok(employeeList);
    }

#2

Based on what @bricelam answered here, can you try this?

根据@bricelam在这里的回答,你能试试吗?

     public Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
    {
        var cmd = _context.Database.GetDbConnection().CreateCommand();
        cmd.CommandText = "GetEmployees";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
        cmd.Parameters.AddWithValue("@PageSize", pageSize);

        try
        {
          //...
        }
   }

Let me know if anything happens.

如果发生任何事情,请告诉我。

#3

Partially solved riddle:

部分解决的谜语:

    // GET: api/Employees/pageIndex/1/pageSize/1
    [HttpGet("pageIndex/{pageIndex}/pageSize/{pageSize}")]
    public async Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
    {
        _context.Database.OpenConnection();

        var cmd = _context.Database.GetDbConnection().CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        /*
         * it's the same, it also works with these lines
                    SqlParameter pageIndexParam = new SqlParameter("PageIndex", pageIndex);
                    SqlParameter pageSizeParam = new SqlParameter("PageSize", pageSize);
                    cmd.Parameters.Add(pageIndexParam);
                    cmd.Parameters.Add(pageSizeParam);
        */

        cmd.CommandText = "GetEmployees"; // The name of the stored procedure

        // the 2 parameters to be passed to the procedure
        var param = cmd.CreateParameter();
        param.ParameterName = "@PageIndex";
        param.Value = pageIndex;
        cmd.Parameters.Add(param);

        var param2 = cmd.CreateParameter();
        param2.ParameterName = "@PageSize";
        param2.Value = pageSize;
        cmd.Parameters.Add(param2);




        try
        {
            System.Data.Common.DbDataReader dr = cmd.ExecuteReader();
            List<Employee> listEmp = new List<Employee>();

            while (dr.Read())
            {
                // Is there a faster way to read the whole record?
                // Or should I write a line for each field?
                Employee emp = new Employee();
                //emp.ID = System.Int32.Parse(dr["id"].ToString());
                emp.ID = 1; //  it crashes after this line
                emp.Fname = dr["FName"].ToString(); // System.IndexOutOfRangeException: FName

                /*
                 * it doesn't see the columns :-(
                                    emp.email = dr["email"].ToString();
                                    emp.Lname = dr["LName"].ToString();
                */
                listEmp.Add(emp);
                dr.NextResult();
            }
            return Ok(listEmp);
        }
        catch (Exception ex)
        {
            // how can I return an error in json format?
            throw;
        }



    }

Now it crashes later When it tries to read the record

现在它在以后崩溃当它试图读取记录时

emp.Fname = dr["FName"].ToString();

System.IndexOutOfRangeException: FName

it doesn't see the columns :-(

它没有看到列:-(