public static List <YogaClass> LoadAllClasses()
{//method used to load clases from data base in lists
List <YogaClass> classes = new List <YogaClass>();
List <YogaNames> teachers = ListTeachers();
OleDbConnection myConnection = GetConnection();
string myQuery = "SELECT * FROM YogaClass WHERE NOW() <= ClassStartingTime ORDER BY ClassStartingTime";
OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection);
try
{
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
YogaNames currentTeacher = FindTeacher(teachers, int.Parse(myReader["StaffID"].ToString()));
YogaClass c = new YogaClass((int.Parse(myReader["ClassID"].ToString())), DateTime.Parse(myReader["ClassStartingTime"].ToString()), myReader["ClassName"].ToString(), currentTeacher, (int.Parse(myReader["ClassLevel"].ToString())), (myReader["ClassDuration"].ToString()), myReader["ClassDescription"].ToString(), (int.Parse(myReader["ClassPlacesAvailable"].ToString())));
classes.Add(c);
}
return(classes);
}
catch (Exception ex)
{
Console.WriteLine("Exception in DBHandler", ex);
return(null);
}
finally
{
myConnection.Close();
}
}
public static List <YogaNames> ListUsers()
{ //Method used to list users
OleDbConnection myConnection = GetConnection();
string myQuery = "SELECT * FROM YogaUser";
OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection);
List <YogaNames> t = new List <YogaNames>();
try
{
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
YogaNames w = new YogaNames((int.Parse(myReader["ID"].ToString())), (myReader["Name"].ToString()), (myReader["Surname"].ToString()));
t.Add(w);
}
return(t);
}
catch (Exception ex)
{
Console.WriteLine("Exception in DBHandler", ex);
return(null);
}
finally
{
myConnection.Close();
}
}
public static List <YogaComment> LoadAllComents()
{//Method used to read comments
List <YogaComment> comments = new List <YogaComment>();
List <YogaNames> users = ListUsers();
OleDbConnection myConnection = GetConnection();
string myQuery = "SELECT * FROM YogaComments";
OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection);
try
{
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
YogaNames currentUser = FindTeacher(users, int.Parse(myReader["YogaUserID"].ToString()));
YogaComment c = new YogaComment((int.Parse(myReader["CommentID"].ToString())), currentUser, (myReader["Comment"].ToString()), (DateTime.Parse(myReader["AddDate"].ToString())));
comments.Add(c);
}
return(comments);
}
catch (Exception ex)
{
Console.WriteLine("Exception in DBHandler", ex);
return(null);
}
finally
{
myConnection.Close();
}
}
public static List <YogaWorkshop> LoadBookedWorkshops(string username, int wStatus)
{//method used to load booked workshops from data base in lists
List <YogaWorkshop> workshop = new List <YogaWorkshop>();
List <YogaNames> teachers = ListTeachers();
OleDbConnection myConnection = GetConnection();
string myQuery = "SELECT * FROM YogaWorkshop LEFT JOIN YogaWorkshopReservation ON YogaWorkshop.WorkshopID=YogaWorkshopReservation.YogaWorkshopID WHERE WClientUsername = '******' AND WorkshopWaitinglistStatus =" + wStatus + ";";
OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection);
try
{
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
YogaNames currentTeacher = FindTeacher(teachers, int.Parse(myReader["StaffID"].ToString()));
YogaWorkshop w = new YogaWorkshop((int.Parse(myReader["WorkshopID"].ToString())), DateTime.Parse(myReader["WorkshopTime"].ToString()), myReader["WorkshopName"].ToString(), currentTeacher, (myReader["WorkshopDuration"].ToString()), myReader["WorkshopDescription"].ToString(), (int.Parse(myReader["WorkshopPlacesAvailable"].ToString())));
workshop.Add(w);
}
return(workshop);
}
catch (Exception ex)
{
Console.WriteLine("Exception in DBHandler", ex);
return(null);
}
finally
{
myConnection.Close();
}
}
public static List <YogaClass> LoadBookedClasses(string username, int wStatus)
{//method used to load booked clases from data base in lists
List <YogaClass> classes = new List <YogaClass>();
List <YogaNames> teachers = ListTeachers();
OleDbConnection myConnection = GetConnection();
string myQuery = "SELECT * FROM YogaClass LEFT JOIN YogaClassReservation ON YogaClass.ClassID=YogaClassReservation.YogaClassID WHERE CClientUsername = '******' AND ClassWaitinglistStatus =" + wStatus + ";";
OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection);
try
{
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
YogaNames currentTeacher = FindTeacher(teachers, int.Parse(myReader["StaffID"].ToString()));
YogaClass c = new YogaClass((int.Parse(myReader["ClassID"].ToString())), DateTime.Parse(myReader["ClassStartingTime"].ToString()), myReader["ClassName"].ToString(), currentTeacher, (int.Parse(myReader["ClassLevel"].ToString())), (myReader["ClassDuration"].ToString()), myReader["ClassDescription"].ToString(), (int.Parse(myReader["ClassPlacesAvailable"].ToString())));
classes.Add(c);
}
return(classes);
}
catch (Exception ex)
{
Console.WriteLine("Exception in DBHandler", ex);
return(null);
}
finally
{
myConnection.Close();
}
}
public static List <YogaWorkshop> LoadAllWorkshops()
{//method used to load workshops from data base in lists
List <YogaWorkshop> workshop = new List <YogaWorkshop>();
List <YogaNames> teachers = ListTeachers();
OleDbConnection myConnection = GetConnection();
string myQuery = "SELECT * FROM YogaWorkshop WHERE NOW() <= WorkshopTime ORDER BY WorkshopTime";
OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection);
try
{
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
YogaNames currentTeacher = FindTeacher(teachers, int.Parse(myReader["StaffID"].ToString()));
YogaWorkshop w = new YogaWorkshop((int.Parse(myReader["WorkshopID"].ToString())), DateTime.Parse(myReader["WorkshopTime"].ToString()), myReader["WorkshopName"].ToString(), currentTeacher, (myReader["WorkshopDuration"].ToString()), myReader["WorkshopDescription"].ToString(), (int.Parse(myReader["WorkshopPlacesAvailable"].ToString())));
workshop.Add(w);
}
return(workshop);
}
catch (Exception ex)
{
Console.WriteLine("Exception in DBHandler", ex);
return(null);
}
finally
{
myConnection.Close();
}
}
public YogaComment(int cId, YogaNames n, string c, DateTime d)
{
commnetId = cId;
userName = n;
comment = c;
addTime = d;
}
public YogaWorkshop(int id, DateTime da, string na, YogaNames t, string dur, string des, int pla)
{
workshopId = id;
workshopTime = da;
workshopName = na;
workshopTeacher = t;
workshopDuration = dur;
workshopDescription = des;
workshopPlacesLeft = pla;
}
public YogaClass(int id, DateTime da, string na, YogaNames t, int lev, string dur, string des, int pla)
{
classId = id;
classTime = da;
className = na;
classTeacher = t;
classLevel = lev;
classDuration = dur;
classDescription = des;
classPlacesLeft = pla;
}