public List <string> GetRoomRates(int roomId)
{
var myRoomRates = new List <string>();
string query = "SELECT room.roomNumber, rt.roomType, rate.rate, rate.description " +
"FROM sad2_db.room room " +
"INNER JOIN sad2_db.room_type rt " +
"ON rt.id = room.roomTypeId " +
"JOIN sad2_db.room_rate rate " +
"ON rate.room_type_id = rt.id " +
"WHERE room.id = '" + roomId + "' ";
using (MySqlDataAdapter blah = new MySqlDataAdapter(query, conn))
{
try
{
DataTable dt = new DataTable();
blah.Fill(dt);
foreach (DataRow drow in dt.Rows)
{
RoomTextBoxItems myComboItems = new RoomTextBoxItems();
myRoomRates.Add(drow[2].ToString() + " " + "(" + drow[3].ToString() + ")");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
// Console.WriteLine(ex);
}
}
return(myRoomRates);
}
public List <RoomTextBoxItems> getAllRoomId()
{
var myComboItemsList = new List <RoomTextBoxItems>();
string query = "SELECT id, roomNumber, roomType FROM room";
using (MySqlDataAdapter blah = new MySqlDataAdapter(query, conn))
{
try
{
DataTable dt = new DataTable();
blah.Fill(dt);
foreach (DataRow drow in dt.Rows)
{
RoomTextBoxItems myComboItems = new RoomTextBoxItems();
myComboItems.ID = Convert.ToInt32(drow[0]);
myComboItems.RoomNumber = (drow[1]).ToString();
myComboItems.RoomType = (drow[2]).ToString();
myComboItemsList.Add(myComboItems);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
// Console.WriteLine(ex);
}
}
return(myComboItemsList);
}
//SELECT Distinct roomType FROM sad2_db.room order by roomType;
public List <string> getRoomTypes()
{
var myRoomTypes = new List <string>();
string query = "SELECT Distinct roomType FROM sad2_db.room_type order by roomType";
using (MySqlDataAdapter blah = new MySqlDataAdapter(query, conn))
{
try
{
DataTable dt = new DataTable();
blah.Fill(dt);
foreach (DataRow drow in dt.Rows)
{
RoomTextBoxItems myComboItems = new RoomTextBoxItems();
myRoomTypes.Add(drow[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
// Console.WriteLine(ex);
}
}
return(myRoomTypes);
}
public List <RoomTextBoxItems> getRoomInfoByType(string roomType)
{
var myComboItemsList = new List <RoomTextBoxItems>();
string query = "SELECT room.id, room.roomNumber, rt.roomType " +
"FROM sad2_db.room_type rt " +
"INNER JOIN sad2_db.room room " +
"ON rt.id = room.roomTypeId " +
"WHERE rt.roomType = '" + roomType + "' ";
//string query = "SELECT id, typeName FROM room WHERE room_type ='"+roomType+"' ";
using (MySqlDataAdapter blah = new MySqlDataAdapter(query, conn))
{
try
{
DataTable dt = new DataTable();
blah.Fill(dt);
foreach (DataRow drow in dt.Rows)
{
RoomTextBoxItems myComboItems = new RoomTextBoxItems();
/*
* myComboItems.ID = Convert.ToInt32(drow[0]);
* myComboItems.roomType = (drow[1]).ToString();
*/
myComboItems.RoomNumber = (drow[1]).ToString();
myComboItemsList.Add(myComboItems);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
// Console.WriteLine(ex);
}
}
return(myComboItemsList);
}