none
Web API with MVC RRS feed

  • Question

  • I need nested json output, my code is working fine if i get the data from one table but i need the data between two linked table for example i need the data like the below :

    in My res.cs i have :

     public class Rooms
        {
            public string Room_No { get; set; }
            public string Description { get; set; }
            public string Need_Confirmation { get; set; }
            public string Location { get; set; }
            public string Status { get; set; }
            public int NoShowAfter { get; set; }
            public string Resource_Name { get; set; }
            public string Full_Path { get; set; }
            public string Attached_Path { get; set; }
            public string Concern_Email { get; set; }

        }

        public class Resources_Links
        {
            public string Room_No { get; set; }
            public string Name { get; set; }
            public string Link { get; set; }


        }

    Ok for Rooms the code below is return fine:

    but how to get same like the above picture :

           //Get All Rooms with Resources
            [HttpGet]
            [ActionName("GetRoomsWithResources")]
            public List<Rooms> GetRoomsWithResources()
            {
                string source4 = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
                SqlConnection conn4 = new SqlConnection(source4);

                SqlCommand cmd4 = new SqlCommand();
                SqlDataReader reader4;

                string sql4 = "Select a.Room_No,a.Description, a.Need_Confirmation, a.Location, a.Status, a.NoShowAfter , c.Resource_Name, c.Full_Path, c.Attached_Path, c.Concern_Email From Rooms a, Room_Resources b, Resources c Where a.ID = b.Room_ID And b.Resource_ID = c.ID";
                cmd4.CommandText = sql4;
                cmd4.CommandType = CommandType.Text;
                cmd4.Connection = conn4;
                Rooms emp4 = null;
                conn4.Open();
                reader4 = cmd4.ExecuteReader();
                List<Rooms> emps4 = new List<Rooms>();
                while (reader4.Read())
                {
                    //read data
                    emp4 = new Rooms();
                    emp4.Room_No = reader4.GetValue(0) as string;
                    emp4.Description = reader4.GetValue(1) as string;
                    emp4.Need_Confirmation = reader4.GetValue(2) as string;
                    emp4.Location = reader4.GetValue(3) as string;
                    emp4.Status = reader4.GetValue(4) as string;
                    emp4.NoShowAfter = Convert.ToInt32(reader4.GetValue(5));
                    emp4.Resource_Name = reader4.GetValue(6) as string;
                    emp4.Full_Path = reader4.GetValue(7) as string;
                    emp4.Attached_Path = reader4.GetValue(8) as string;
                    emp4.Concern_Email = reader4.GetValue(9) as string;

                    emps4.Add(emp4);


                }
                conn4.Close();
                return emps4;

            }

    they are link with column ID

    • Moved by CoolDadTx Saturday, August 12, 2017 6:01 PM ASP.NET related
    Saturday, August 12, 2017 1:47 PM

All replies

  •  public class Rooms
     {
            private List<Resources_Links. links = new List<Resources_Links>();
    
            public string Room_No { get; set; }
            public string Description { get; set; }
            public string Need_Confirmation { get; set; }
            public string Location { get; set; }
            public string Status { get; set; }
            public int NoShowAfter { get; set; }
            public string Resource_Name { get; set; }
            public string Full_Path { get; set; }
            public string Attached_Path { get; set; }
            public string Concern_Email { get; set; }
     
            public List<Resources_Links> Links
            {
                 get {return links;}
                 set {links = value;]
            }
     }

    var link = new Resources_Links();

    //populate link object

    var room =new Room();

    //populate properties.

    var link = new Resources_Links();

    //populate properties 

    room.Links.Add(link);

    It also means you need to query for the child records and add each one to room.Links.

    Saturday, August 12, 2017 2:53 PM
  • Oh btw......

    https://forums.asp.net/1146.aspx/1?MVC

    Saturday, August 12, 2017 2:54 PM
  • Can you please give more explanation 
    Saturday, August 12, 2017 3:56 PM
  • Please post questions related to MVC and ASP.NET in the ASP.NET forums.
    Saturday, August 12, 2017 6:01 PM
  • Can you please give more explanation 

    The example of a Json object shown by you is an object that has the parent object with children objects, an array of ,  within the parent object with the parent acting as a container for the children.

    Any objects with in the parent object container are assumed to be related to the parent.

    So the C# parent object with a List<T> of child objects is the exact duplicate of what the Json object with its child objects are doing. If you take the C# object with its children objects and Json serialize it, then it will have the same Json format as your example.

    So in the middle of populating the parent object in the reader loop, you must have a other function that takes the id of the parent, reads the child records,  creating each child object  adding the child to List<T> and returning the List <T> of child objects to populate the parent's List<T> for those returned child objects.

    And then you add the parent to its List<T>.

    BTW, your code with it being coded in the controller is not optimal coding. The Repository with possibly a DAO pattern should be implemented where the controller of the WebAPI calls the Repository for CRUD operations with the DB.

    http://blog.sapiensworks.com/post/2012/11/01/Repository-vs-DAO.aspx

    Also this, because I have a feeling you are breaking SoC principle in MVC too.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    http://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

     
    Saturday, August 12, 2017 7:09 PM