Forums - C# Corner

Forum guidelines
ikrami sami

ikrami sami

  • 1.5k
  • 27
  • 10.7k

Web API MVC with SQL Server

Aug 12 2017 9:57 AM

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


Answers (6)