public List<LessonCL> SecletLesson()
{
string query = "SELECT * FROM lesson";
// creats a list of lectuer objects
List<LessonCL> allless = new List<LessonCL>();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
Int32 lessonID = dataReader.GetInt32(0);
String lectName = dataReader.GetString(1);
String ygName = dataReader.GetString(2);
String roomsName = dataReader.GetString(3);
String modName = dataReader.GetString(4);
String lessTime = dataReader.GetString(5);
String lessEndTime = dataReader.GetString(6);
String lessDay = dataReader.GetString(7);
LessonCL less = new LessonCL(lessonID, lectName, ygName, roomsName, modName, lessTime,lessEndTime, lessDay);
allless.Add(less);
}
//close Data Reader
dataReader.Close();
//close Connectionon
this.CloseConnection();
}
return allless;
}
public void InsertLess(LessonCL less)
{
//INSERT INTO lesson(idLesson, Lecturer_idLecturer, YearGroup_idYearGroup, Room_idRoom, Module_idModule ) VALUES('0','Nigel Edwards','BSc Computer Science Yr 2','Y106','Database Design')
//Pulls id field from the lecturer table
if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand("SELECT idLecturer from lecturer WHERE lecturerName='" + less.lectName + "'", connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
dataReader.Read();
int lecturerid = dataReader.GetInt32(0);
//close Data Reader
dataReader.Close();
//Pulls id field from the table year group
cmd = new MySqlCommand("SELECT idYearGroup from yeargroup WHERE yearGroupName='" + less.ygName + "'" , connection);
//Create a data reader and Execute the command
dataReader = cmd.ExecuteReader();
dataReader.Read();
int ygid = dataReader.GetInt32(0);
//close Data Reader
dataReader.Close();
//Pulls id field from the room table
cmd = new MySqlCommand("SELECT idRoom from room WHERE roomName='" + less.roomsName + "'", connection);
//Create a data reader and Execute the command
dataReader = cmd.ExecuteReader();
dataReader.Read();
int roomid = dataReader.GetInt32(0);
//close Data Reader
dataReader.Close();
//Pulls id field from module the table
cmd = new MySqlCommand("SELECT idModule from module WHERE moduleName='" + less.modName + "'", connection);
//Create a data reader and Execute the command
dataReader = cmd.ExecuteReader();
dataReader.Read();
int modid = dataReader.GetInt32(0);
//close Data Reader
dataReader.Close();
//cmd =new MySqlCommand("SELECT idLesson from lesson WHERE startTime='" + startTime + "'", connection);
//cmd = new MySqlCommand("SELECT idlesson from lesson WHERE endtime='" + endTime + "'", connection);
// cmd = new MySqlCommand("SELECT idLesson from lesson WHERE day ='" + day + "'", connection);
//create command and assign the query and connection from the constructor
string qureyLesson = "INSERT INTO lesson(idLesson, Lecturer_idLecturer, YearGroup_idYearGroup, Room_idRoom, Module_idModule, startTime, endTime, day ) VALUES('" + less.lessID + "','" + lecturerid + "','" + ygid + "','" + roomid + "','" + modid + "','" + less.startTime +"','"+ less.endTime +"','" +less.Day+ "')";
cmd = new MySqlCommand(qureyLesson, connection);
//NOTE!! throws exaption as to the defluat data
//Execute command
cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
}