Help in Return Multiple Rows in API

Question

I have the below API which return just last records in the Table,  how to let it to return mutlpile records depend on the select statements :

 

 

[HttpGet]

[ActionName("GetReservationByID")]

public Reservation Get(int id)

{

string source = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

SqlConnection conn = new SqlConnection(source);

SqlCommand cmd = new SqlCommand();

SqlDataReader reader;

string sql = "Select s.Id,a.Room_No ,s.Room_Description,s.Room_Capacity, b.Description, s.Location,s.Start_Date,s.Start_Time,s.End_Time,s.Meeting_Title,s.Reservation_Reason, s.Status,s.Attendance,s.Remarks,s.Mail_To,s.Mail_CC from Reservation s , Rooms a, Room_Type b Where s.Room_No = a.ID And s.Room_Type = b.ID AND s.Id >" + id + "";

cmd.CommandText = sql;

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

Reservation emp = null;

conn.Open();

reader = cmd.ExecuteReader();

while (reader.Read())

{

//read data

emp = new Reservation();

emp.Id = Convert.ToInt32(reader.GetValue(0));

emp.Room_No = reader.GetValue(1).ToString();

emp.Room_Description = reader.GetValue(2) as string;//reader.GetValue(3).ToString();

emp.Room_Capacity = (reader.GetValue(3) as int?) ?? 0; //Convert.ToInt32(reader.GetValue(4));

emp.Room_Type = reader.GetValue(4) as string;//reader.GetValue(5).ToString();

emp.Location = reader.GetValue(5) as string; //reader.GetValue(6).ToString();

emp.Start_Date = reader.GetValue(6) as string; //reader.GetValue(7).ToString();

emp.Start_Time = reader.GetValue(7) as string; //reader.GetValue(8).ToString();

emp.End_Time = reader.GetValue(8) as string; //reader.GetValue(9).ToString();

emp.Meeting_Title = reader.GetValue(9) as string; //reader.GetValue(10).ToString();

emp.Reservation_Reason = reader.GetValue(10) as string; //reader.GetValue(11).ToString();

emp.Status = reader.GetValue(11) as string; //reader.GetValue(12).ToString();

emp.Attendance = (reader.GetValue(12) as int?) ?? 0;//Convert.ToInt32(reader.GetValue(13));

emp.Remarks = reader.GetValue(13) as string;//reader.GetValue(14).ToString();

emp.Mail_To = reader.GetValue(14) as string;//reader.GetValue(15).ToString();

emp.Mail_CC = reader.GetValue(15) as string;//reader.GetValue(16).ToString();

}

conn.Close();

return emp;

}