asp.net-core - 如何使用 Database.GetDbConnection() 将 SQL Server 存储过程的结果存储到视图模型中

Question

我必须执行一个存储过程才能从数据库中获取所需的结果，并且该结果必须使用 list(model) 显示在 html 表中。

我正在使用模型类MissingAttendanceVM：

public class MissingAttendanceVM
{
    public string TxtAttDate { get; set; }
}

存储过程：

ALTER PROCEDURE [dbo].[goGetDepotsMissingAttPopup]
    (@DepotNo int,
     @DepartmentID int)
AS 
BEGIN
    SELECT AttendanceDate 
    FROM Attendance 
    WHERE DepotNo = @DepotNo  
      AND DepartmentId = @DepartmentID   // The result is returned as datetime
END

现在我正在尝试将结果放入列表视图模型类中：

public ICollection<MissingAttendanceVM> GetMissingAttendanceDatesByDepo(int depoNo = 0, int department = 0)
{
    using (var cmd = _db.Database.GetDbConnection().CreateCommand())
    {
        cmd.CommandText = "exec goGetDepotsMissingAttPopup";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        SqlParameter parDepot = new SqlParameter("@DepotNo", depoNo);
        SqlParameter parDept = new SqlParameter("@DepartmentID", department);
                
        cmd.Parameters.Add(parDepot);
        cmd.Parameters.Add(parDept);

        _db.Database.OpenConnection();

        using (var result = cmd.ExecuteReader())
        {
            List<MissingAttendanceVM> datelist= new List<MissingAttendanceVM>();
            datelist = //I am trying  to store the result from datareader
        }
    }

    return datelist
}

如何将结果带入 datelist 变量？

谢谢，艾伦

标签： asp.net-corestored-procedures