Esempio n. 1
0
        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;
        }
Esempio n. 2
0
        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();
            }
        }