Web API MVC with SQL Server

ikrami sami

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 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 emps4 = new List();
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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Catcher Wong9y ago
Accepted answer
Here is the whole sample the same as yours.

Define entities first :
public class Rooms
{
    public string Resource_ID { get; set; }
    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 int Capacity { get; set; }
}

public class RoomsDTO
{
    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 int Capacity { get; set; }
    public IList Default_Resources { get; set; }
}

public class Resources
{
    public string Name { get; set; }
    public string Link { get; set; }
}
And the controller looks like this
[HttpGet]
[ActionName("GetRoomsWithResources")]
public List 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, b.Resource_ID From Rooms a, Room_Resources b Where a.ID = b.Room_ID";
    cmd4.CommandText = sql4;
    cmd4.CommandType = CommandType.Text;
    cmd4.Connection = conn4;
    Rooms emp4 = null;
    conn4.Open();
    reader4 = cmd4.ExecuteReader();
    List emps4 = new List();
    //this time get the rooms entity that you need
    while (reader4.Read())
    {
        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));
        //the Resource_ID is important for the next step to get the Default_Resource
        emp4.Resource_ID = reader4.GetValue(6) as string;
        emps4.Add(emp4);
    }
    //define the result we need to return
    var dto = new List();
    foreach (var item in emps4)
    {
        var resouceList = new List();

        #region this step mainly to get the Default_Resources
        var tmpCmd = new SqlCommand();
        var tmpSql = string.Format(@"Select c.Resource_Name, c.Full_Path, c.Attached_Path, c.Concern_Email From Room_Resources b Where  b.Resource_ID = c.ID and b.Resource_ID = {0}", item.Resource_ID);
        tmpCmd.CommandText = tmpSql;
        tmpCmd.CommandType = CommandType.Text;
        tmpCmd.Connection = conn4;
        var tmpReader = tmpCmd.ExecuteReader();

        while (tmpReader.Read())
        {
            //read data
            var resource = new Resources();
            resource.Name = tmpReader.GetValue(0) as string;
            resource.Link = tmpReader.GetValue(1) as string;
            resouceList.Add(resource);
        }
        #endregion

        dto.Add(new RoomsDTO
        {
            Room_No = item.Room_No,
            Description = item.Description,
            Location = item.Location,
            Need_Confirmation = item.Need_Confirmation,
            NoShowAfter = item.NoShowAfter,
            Status = item.Status,
            Default_Resources = resouceList
        });
    }

    conn4.Close();
    return dto;
}
Hope this can help you to understand!!
0
ikrami sami9y ago
Thank you so much
0
ikrami sami9y ago
this is my code, can you just to tell me where and what to add, please please same as i do it to understand :

[HttpGet]
[ActionName("GetRoomsWithResources")]
public List 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 emps4 = new List();
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;
//Resource Details - Karim
//End Resource Details - Karim
emps4.Add(emp4);
}
conn4.Close();
return emps4;
}

0
Catcher Wong9y ago
Here is a example to show:
public ActionResult Test()
{
    using (IDbConnection conn = new SqlConnection("connection string"))
    {
        conn.Open();
        string sql = @"select Room_No,Description,Need_Confirmation,Location,Status,NoShowAfter,Capacity from table1 where xx = xx";

        //get the other basic information first
        var roomList = conn.Query(sql).ToList();

        //loop the above result , and try to get the resouces
        foreach (var item in roomList)
        {
            var tmpSql = "select * from table2 where id = @id";
            item.Default_Resources = conn.Query(tmpSql, new
{
id = item.Room_No //this is based on your table's relation
}).ToList();
        }
    }
}
0
ikrami sami9y ago
Would you please tell me where to put the query for Resource list as it is select from table
0
Catcher Wong9y ago
@Ikrami Sami

You can try to redefine your Rooms class like this :
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 int Capacity{get;set;}
    public IList Default_Resources{get;set;}
}

public class Resources
{
    public string Name { get; set; }
    public string Link { get; set; }
}
When you query the data , you should query thoes fields as first

Room_No,Description,Need_Confirmation,Location,Status,NoShowAfter,Capacity

and you will get a list of Rooms class , then loop this list to get the Resources for each room based on the relations so that you can get the result like your picture shows.
0