locked
Dataset RRS feed

  • Question

  • User1979860870 posted

    Hi

       I have below code. I want how to read data from Dataset & pass values to Object 

    public partial class Employee
        {
            public string ID { get; set; }
            public string Name { get; set; }
            public virtual Department Department { get; set; }
        }
        public partial class Department
        {
            public int ID { get; set; }
            public string ShortName { get; set; }
            public string Description { get; set; }
    
            public virtual ICollection<Employee> Employees { get; set; }
        }
    
    --------------------------------------------
    public class EmployeeController : ApiController
        {
            //GET: api/Employees
            public IHttpActionResult GetEmployees()
            {
                try
                {
                    DataSet ds = Common.DataRecords("Select T0.Id,T0.Name,T1.Description from Employees T0 inner join Departments T1 on T0.[DepartmentId] = T1.ID");
    
                    
                    return Ok(emps);
                }
                catch (Exception ex)
                {
                    return BadRequest();
                }
            }
    }

    Thanks

    Sunday, March 7, 2021 1:58 PM

All replies

  • User475983607 posted

    You did not share the source code for Common.DataRecords().   Also, the SQL has fewer columns than the classes have properties.  It is very confusing why you are not sharing the actual code.   Is this another one of your posts where you copied code from the internet and have no idea what it does???

    I recommend basic troubleshooting and debugging.  This your code and you should know how the code is intended to function.  Therefore you should know when the code stops behaving as expected while single stepping with the debugger.  Make an effort...

    Sunday, March 7, 2021 3:50 PM
  • User1979860870 posted

    Hi

    public static DataSet DataRecords(string SrcSql, SrcType SourceType = SrcType.SqlQuery)
            {
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(SrcSql, @"Server=Jagjit\NavDemo;Database=EInvoicing;User ID=sa;Password=sa@2012");
                da.Fill(ds);
                return ds;
            }

    Thanks

    Monday, March 8, 2021 9:08 AM
  • User-1330468790 posted

    Hi jagjit saini, 

     

    First of all, you have to understand that the actual working data collection is the "DataTable" which is the first child of the tables of the data set.

    In codes, it is "ds.Tables[0]".

    You could refer to below codes which is contructed with your original codes.

    I have used below simulation of data as if it is selected from database with column names.

    DataRecords():

    protected DataSet DataRecords()
            {
                DataSet ds = new DataSet();
    
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[] { 
                    new DataColumn("EmployeeID",typeof(string)),
                    new DataColumn("EmployeeName",typeof(string)),
                    new DataColumn("DepartmentID",typeof(int)),
                    new DataColumn("ShortName",typeof(string)),
                    new DataColumn("Description",typeof(string))
                });
    
                dt.Rows.Add("1","Employee1",1,"Depart1","Depart1_Description");
                dt.Rows.Add("2", "Employee2", 1, "Depart1", "Depart1_Description");
                dt.Rows.Add("3", "Employee3", 1, "Depart1", "Depart1_Description");
                dt.Rows.Add("4", "Employee4", 1, "Depart1", "Depart1_Description");
                dt.Rows.Add("5", "Employee5", 1, "Depart1", "Depart1_Description");
                dt.Rows.Add("6", "Employee6", 1, "Depart1", "Depart1_Description");
                dt.Rows.Add("7", "Employee7", 2, "Depart2", "Depart2_Description");
                dt.Rows.Add("8", "Employee8", 2, "Depart2", "Depart2_Description");
                dt.Rows.Add("9", "Employee9", 2, "Depart2", "Depart2_Description");
                dt.Rows.Add("10", "Employee10", 2, "Depart2", "Depart2_Description");
                 
                ds.Tables.Add(dt);
    
    
                return ds;
            }

    GetEmployees():

    //GET: api/Employees
            public IHttpActionResult GetEmployees()
            {
                try
                {
                    DataSet ds = DataRecords();
    
                    DataTable dt = ds.Tables[0];
    
                    // Select All of Departments with groupping Employee list 
                    var departs = dt.AsEnumerable()
                                    .GroupBy(r => r.Field<int>("DepartmentID"))
                                    .Select(g => {
                                        var depart = new Department();
                                        depart.ID = g.Key;
                                        depart.ShortName = g.Select(e => e.Field<string>("ShortName")).FirstOrDefault();
                                        depart.Description = g.Select(e => e.Field<string>("Description")).FirstOrDefault();
                                        depart.Employees = g.ToList()
                                          .Select(e => new Employee()
                                          {
                                              ID = e.Field<string>("EmployeeID"),
                                              Name = e.Field<string>("EmployeeName"),
                                              Department = depart
                                          }).ToList();
                                        return depart;
                                    }).ToList();
                    // Get the final Employee list
                    List<Employee> finalEmployeesList = new List<Employee>();
    
                    foreach (var depart in departs)
                    {
                        foreach (var employee in depart.Employees)
                        {
                            finalEmployeesList.Add(employee);
                        }
                    }
    
                    return Ok(finalEmployeesList);
                }
                catch (Exception ex)
                {
                    return BadRequest();
                }
            }

     

    Hope helps.

    Best regards,

    Sean

    Friday, March 12, 2021 8:56 AM