//Select statement for the lectuer public List<RoomCL> SelectRoom() { string query = "SELECT * FROM room"; // creats a list of room objects List<RoomCL> allRoom = new List<RoomCL>(); //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(); //Read the data and store them in the list while (dataReader.Read()) { // adds the data in the columes to the class varbiles and pass them in Int32 roomID = dataReader.GetInt32(0); String rName = dataReader.GetString(1); Int32 capacity = dataReader.GetInt32(2); String rType = dataReader.GetString(2); // add all the curent values to the database RoomCL r = new RoomCL(roomID, rName, rType, capacity); allRoom.Add(r); } //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); } // return all the records in the database for the room return allRoom; }
//Insert statement for room table public void InsertRoom(RoomCL r) { // query to allow data to be placed into the table room on the database from the RoomCL class string queryRoom = "INSERT INTO room (roomName, roomCapacity, roomType) VALUES('" + r.rName + "','" + r.capacity + "','" + r.rType + "')"; //open connection if (this.OpenConnection() == true) { //create command and assign the query and connection from the constructor MySqlCommand cmd = new MySqlCommand(queryRoom, connection); //Execute command cmd.ExecuteNonQuery(); //close connection this.CloseConnection(); } }