private void saveButton_Click(object sender, EventArgs e) { string sqlCommandText = "SELECT workingHours FROM All_Classrooms WHERE number = @oldNumber"; SqlCommand sql = new SqlCommand(sqlCommandText, parent.Connection); sql.Parameters.Add(new SqlParameter("@oldNumber", oldClassroomNum)); SqlDataReader rdr = sql.ExecuteReader(); try { if (rdr.Read()) { string[] workingHoursString = ((string)rdr["workingHours"]).Split('-'); if (int.Parse(changeStartWorkingTextBox.Text) > int.Parse(workingHoursString[0]) || int.Parse(changeEndWorkingTextBox.Text) < int.Parse(workingHoursString[1])) { // working hours changed rdr.Close(); sqlCommandText = "SELECT * FROM All_Reservations WHERE" + " classroomNumberID = (SELECT classroomID FROM All_Classrooms WHERE number = @oldNumber)"; sql.CommandText = sqlCommandText; rdr = sql.ExecuteReader(); List <int> resIDsForDelete = new List <int>(), resIDsForUpdateStart = new List <int>(), resIDsForUpdateEnd = new List <int>(); List <string> resTimeUpdateStart = new List <string>(), resTimeUpdateEnd = new List <string>(); while (rdr.Read()) { string[] dateString = ((string)rdr["ev_date"]).Split('.'); DateTime date1 = new DateTime(int.Parse(dateString[2]), int.Parse(dateString[1]), int.Parse(dateString[0])), nowDateTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); if (date1.CompareTo(nowDateTime) > -1) { // if date of reservation is grater than today // delete or change ev time string[] res_time = ((string)rdr["ev_time"]).Split('-'); if (int.Parse(changeStartWorkingTextBox.Text) >= int.Parse(res_time[1]) || int.Parse(changeEndWorkingTextBox.Text) <= int.Parse(res_time[0])) { // if reservation time is out of classroom working time resIDsForDelete.Add((int)rdr["reservationID"]); } else if (int.Parse(changeStartWorkingTextBox.Text) > int.Parse(res_time[0])) { // if reservation start time is less than new start working hour resIDsForUpdateStart.Add((int)rdr["reservationID"]); resTimeUpdateStart.Add((string)rdr["ev_time"]); } else if (int.Parse(changeEndWorkingTextBox.Text) < int.Parse(res_time[1])) { // if reservation start time is grater than new end working hour resIDsForUpdateEnd.Add((int)rdr["reservationID"]); resTimeUpdateEnd.Add((string)rdr["ev_time"]); } } } rdr.Close(); if (resIDsForDelete.Count > 0) { sqlCommandText = "DELETE FROM All_Reservations WHERE reservationID = @reservationID"; sql.CommandText = sqlCommandText; sql.Parameters.Clear(); for (int i = 0; i < resIDsForDelete.Count; i++) { sql.Parameters.Add(new SqlParameter("@reservationID", resIDsForDelete[i])); sql.ExecuteNonQuery(); sql.Parameters.Clear(); } } if (resIDsForUpdateEnd.Count > 0) { sqlCommandText = "UPDATE All_Reservations SET ev_time = @ev_time WHERE reservationID = @reservationID"; sql.CommandText = sqlCommandText; sql.Parameters.Clear(); for (int i = 0; i < resIDsForUpdateEnd.Count; i++) { string[] time = resTimeUpdateEnd[i].Split('-'); sql.Parameters.Add(new SqlParameter("@ev_time", time[0] + '-' + changeEndWorkingTextBox.Text)); sql.Parameters.Add(new SqlParameter("@reservationID", resIDsForUpdateEnd[i])); sql.ExecuteNonQuery(); sql.Parameters.Clear(); } } if (resIDsForUpdateStart.Count > 0) { sqlCommandText = "UPDATE All_Reservations SET ev_time = @ev_time WHERE reservationID = @reservationID"; sql.CommandText = sqlCommandText; sql.Parameters.Clear(); for (int i = 0; i < resIDsForUpdateStart.Count; i++) { string[] time = resTimeUpdateStart[i].Split('-'); sql.Parameters.Add(new SqlParameter("@ev_time", changeStartWorkingTextBox.Text + '-' + time[1])); sql.Parameters.Add(new SqlParameter("@reservationID", resIDsForUpdateStart[i])); sql.ExecuteNonQuery(); sql.Parameters.Clear(); } } } } } catch (Exception) { } finally { if (!rdr.IsClosed) { rdr.Close(); } } // command sqlCommandText = "UPDATE All_Classrooms SET number = @number, blackboard = @blackboard, computers = @computers, " + "projector = @projector, laboratory = @laboratory, capacity = @capacity, workingHours = @workingHours, comment = @comment, notice = @notice" + " WHERE number = @oldNumber"; // make sql command sql.CommandText = sqlCommandText; sql.Parameters.Clear(); sql.Parameters.Add(new SqlParameter("@number", changeCNumTextBox.Text)); sql.Parameters.Add(new SqlParameter("@blackboard", changeBlackboardCheckBox.Checked)); sql.Parameters.Add(new SqlParameter("@computers", changeComputersCheckBox.Checked)); sql.Parameters.Add(new SqlParameter("@projector", changeProjectorCheckBox.Checked)); sql.Parameters.Add(new SqlParameter("@laboratory", changeLaboratoryCheckBox.Checked)); sql.Parameters.Add(new SqlParameter("@capacity", int.Parse(changeCapacityTextBox.Text))); sql.Parameters.Add(new SqlParameter("@workingHours", changeStartWorkingTextBox.Text + "-" + changeEndWorkingTextBox.Text)); sql.Parameters.Add(new SqlParameter("@comment", changeCommentTextBox.Text)); sql.Parameters.Add(new SqlParameter("@notice", changeNoticeTextBox.Text)); sql.Parameters.Add(new SqlParameter("@oldNumber", oldClassroomNum)); // execute command sql.ExecuteNonQuery(); parent.setNewClassroomsForAutocomplete(); this.Hide(); }