locked
how can I store the result from stored procedure into View Model RRS feed

  • Question

  • User-1355965324 posted

    I am struggling  to store the result  from stored procedure into List<Model> , I have the following model class

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

    I have the stored procedure 

    Alter  PROCEDURE [dbo].[goGetDepotsMissingAttPopup](
    @DepotNo int
    ,@DepartmentID int
     )
    
    AS 
    BEGIN
      Select  AttendanceDate from Attendance where DepotNo =  @DepotNo  And DepartmentId = @DepartmentID // The result is coming as datetime
    END
     
    

    I have  a method in my repo class as given below .How to return the  ICollection List  from the result from stored procedure

    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);
                    SqlParameter parDate = new SqlParameter("@Attdate", attdate);
                    cmd.Parameters.Add(parDepot);
                    cmd.Parameters.Add(parDept);
                    _db.Database.OpenConnectionAsync();
                    using (var result = cmd.ExecuteReader())
                    {
                        List<MissingAttendanceVM> datelist= new List<MissingAttendanceVM>();
                        datelist = //I am trying  to store the result from datareader
                    }
    
    
                }
    return datelist
    
    }
    

    Thursday, April 29, 2021 3:52 PM

Answers

  • User-474980206 posted

    The docs are pretty clear

    using (var result = cmd.ExecuteReader())
    {
        List<MissingAttendanceVM> datelist= new List<MissingAttendanceVM>();
         while (result.Read())
         {
               datelist.Add(result.GetString(0));
         } 
    }

    Not sure why you did not want a list of datetime 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 29, 2021 9:29 PM

All replies

  • User-474980206 posted

    The docs are pretty clear

    using (var result = cmd.ExecuteReader())
    {
        List<MissingAttendanceVM> datelist= new List<MissingAttendanceVM>();
         while (result.Read())
         {
               datelist.Add(result.GetString(0));
         } 
    }

    Not sure why you did not want a list of datetime 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 29, 2021 9:29 PM
  • User1686398519 posted

    Hi polachan, 

    While a DataReader is open, you can retrieve schema information about the current result set using the GetSchemaTable method.

    1. Each column of the schema table maps to a property of the columns returned in the rows of the result set, where the ColumnName is the name of the property and the value of the column is the value of the property.
    2. Getting schema information from the DataReader
    using (var result = cmd.ExecuteReader())
    {
            List<MissingAttendanceVM> datelist= new List<MissingAttendanceVM>();
            DataTable schemaTable = result.GetSchemaTable();
    List<MissingAttendanceVM> datelist= new List<MissingAttendanceVM>(); foreach (DataRow row in schemaTable.Rows) { foreach (DataColumn column in schemaTable.Columns) {
    datelist.Add(new MissingAttendanceVM{TxtAttDate=row[column]}); } } }

    Best Regards,

    YihuiSun

    Friday, April 30, 2021 1:19 AM
  • User-1355965324 posted

    Thanks to All for the help

    Friday, April 30, 2021 2:21 PM