public static void Update(Client client)
{
var sql = string.Format("Update Clients set Name = '{0}'" +
", Email = '{1}' , Phone = '{2}' Where ClientID={3}", client.Name, client.Email, client.Phone, client.ClientId);
MyDB.GetInstance().ExecuteSql(sql);
}
public static void Create(Client client)
{
var db = MyDB.GetInstance();
var sql =
string.Format("INSERT INTO Clients VALUES ('{0}' , '{1}', {2})", client.Name, client.Email, client.Phone);
db.ExecuteSql(sql);
}
public static void Delete(int id)
{
var db = MyDB.GetInstance();
var sql =
string.Format("Delete From Clients where ClientID = {0}", id);
db.ExecuteSql(sql);
}
public static void Delete(Hall hall)
{
var db = MyDB.GetInstance();
var sql =
string.Format("Delete From Halls where HallID = {0}", hall.HallID);
db.ExecuteSql(sql);
}
public static void Delete(Reservations reservations)
{
var db = MyDB.GetInstance();
var sql =
string.Format("Delete From Reservations where ResID = {0}", reservations.ReservationId);
db.ExecuteSql(sql);
}
public static void Create(Hall hall)
{
var db = MyDB.GetInstance();
var sql =
string.Format("INSERT INTO Halls VALUES ('{0}' , '{1}')", hall.HallName, hall.MaxOccupancy);
db.ExecuteSql(sql);
}
public static void Create(Reservations reservations)
{
var db = MyDB.GetInstance();
var sql =
string.Format("INSERT INTO Reservations VALUES ('{0}' , '{1}', '{2}')", reservations.ClientId, reservations.HallId, reservations.Date);
db.ExecuteSql(sql);
}
public static Hall GetHall(int id)
{
var db = MyDB.GetInstance();
var sql =
string.Format("Select * from Halls where HallID = {0}", id);
var results = db.ExecuteSelectSql(sql);
if (results.HasRows)
{
results.Read();
return(new Hall
{
HallID = (int)results["HallID"],
HallName = results["HallName"].ToString(),
MaxOccupancy = (int)results["MaxOccupancy"]
});
}
return(null);
}
public static Client GetClient(int id)
{
var db = MyDB.GetInstance();
var sql =
string.Format("Select * from Clients where ClientID = {0}", id);
var results = db.ExecuteSelectSql(sql);
if (results.HasRows)
{
results.Read();
return(new Client
{
ClientId = (int)results["ClientID"],
Name = results["Name"].ToString(),
Email = results["Email"].ToString(),
Phone = results["Phone"].ToString()
});
}
return(null);
}
//
public static List <Hall> GetHalls()
{
var halls = new List <Hall>();
var results =
MyDB.GetInstance().ExecuteSelectSql("select * from Halls");
while (results.Read())
{
var hall = new Hall()
{
HallID = (int)results["HallID"],
HallName = results["HallName"].ToString(),
MaxOccupancy = (int)results["MaxOccupancy"]
};
halls.Add(hall);
}
return(halls);
}
// ClientId = (int)results["ClientID"],
//HallId = (int)results["HallID"],
public static Reservations GetAvailability(Hall hall, Hall date)
{
var db = MyDB.GetInstance();
var sql =
string.Format("Select * from Reservations where HallID = {0} AND Date ={1}", hall.HallID, date);
var results = db.ExecuteSelectSql(sql);
if (results.HasRows)
{
results.Read();
return(new Reservations
{
ReservationId = (int)results["ResID"],
ClientId = (int)results["ClientID"],
HallId = (int)results["HallID"],
Date = (DateTime)results["Date"]
});
}
return(null);
}
public static Reservations GetReservation(int id)
{
var db = MyDB.GetInstance();
var sql =
string.Format("select * from Reservations where ResID = {0}", id);
var results = db.ExecuteSelectSql(sql);
if (results.HasRows)
{
results.Read();
return(new Reservations
{
ReservationId = (int)results["ResID"],
ClientId = (int)results["ClientID"],
HallId = (int)results["HallID"],
Date = (DateTime)results["Date"]
});
}
return(null);
}
public static List <Client> GetClients()
{
var clients = new List <Client>();
var results =
MyDB.GetInstance().ExecuteSelectSql("select * from Clients");
while (results.Read())
{
var client = new Client()
{
ClientId = (int)results["ClientID"],
Name = results["Name"].ToString(),
Email = results["Email"].ToString(),
Phone = results["Phone"].ToString()
};
clients.Add(client);
}
return(clients);
}
/*
* SELECT Clients.Name, Halls.HallName, Reservations.Date
* from Reservations inner join Clients
* on Clients.ClientID=Reservations.ClientID
* inner join Halls
* on Halls.HallID=Reservations.HallID
*
*/
//
// To get list of reservations with joins from Halls and Clients Tables
public static List <Reservations> GetReservations()
{
var reservations = new List <Reservations>();
var results =
MyDB.GetInstance().ExecuteSelectSql("SELECT Clients.Name, Halls.HallName, Reservations.Date, Reservations.ResID FROM Reservations inner join Clients on Clients.ClientID=Reservations.ClientID inner join Halls on Halls.HallID=Reservations.HallID");
while (results.Read())
{
var reservation = new Reservations()
{ //column db names
ReservationId = (int)results["ResID"],
ClientName = results["Name"].ToString(),
HallName = results["HallName"].ToString(),
Date = (DateTime)results["Date"]
};
reservations.Add(reservation);
}
return(reservations);
}
public static void Update(Hall hall)
{
var sql = string.Format("Update Halls set HallName = '{0}' , MaxOccupancy = {1} Where HallID = {2}", hall.HallName, hall.MaxOccupancy, hall.HallID);
MyDB.GetInstance().ExecuteSql(sql);
}
public static void Update(Reservations reservations)
{
var sql = string.Format("Update Reservations set ClientID = '{0}' , HallID = {1}, Date = '{2} Where ResID = {3}", reservations.ClientId, reservations.HallId, reservations.Date, reservations.ReservationId);
MyDB.GetInstance().ExecuteSql(sql);
}