//insert user to the table sched2_TA_A - lecturer that doesn't exsit in the table public bool insertToSchecd2(TeachingAssitant ta) { string constring = "Data Source=p17server.database.windows.net;Initial Catalog=P17DATABASE;Persist Security Info=True;User ID=P17;Password=Hadas@2017"; int index; int nextIndex = 0; try { string q = "select MAX(num) from sched2_TA_A"; cn = new SqlConnection(constring); cmd = new SqlCommand(q, cn); cn.Open(); dr = cmd.ExecuteReader(); while (dr.Read()) { index = Int32.Parse(dr[0].ToString()) + 1; nextIndex = index; //in order to get index out of the red() loop } dr.Close(); cn.Close(); } catch (Exception exe) { MessageBox.Show(exe.Message); } try { cn = new SqlConnection(constring); cn.Open(); string q = "insert into [sched2_TA_A] (Num,ID, TA,day) values(@Num , @ID, @TA , @day );"; cmd = new SqlCommand(q, cn); cmd.Parameters.AddWithValue("@Num", nextIndex); cmd.Parameters.AddWithValue("@ID", ta.getID()); cmd.Parameters.AddWithValue("@TA", ta.mergetoOne(ta.getFirstName(), ta.getLastName())); cmd.Parameters.AddWithValue("@day", this.course.getDayL()); //cmd.CommandText = "SELECT SCOPE_IDENTITY()"; cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cn.Close(); dr.Close(); return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); } return(false); }
// to check the available hours of the TA and save then in variable List taHours public void findFreeHours(TeachingAssitant ta) { string constring = "Data Source=p17server.database.windows.net;Initial Catalog=P17DATABASE;Persist Security Info=True;User ID=P17;Password=Hadas@2017"; string Query = "SELECT * from sched2_TA_A WHERE TA ='" + ta.mergetoOne(ta.getFirstName(), ta.getLastName()) + "' and day = '" + this.cmbDay.SelectedItem + "' "; cn = new SqlConnection(constring); cmd = new SqlCommand(Query, cn); cn.Open(); dr = cmd.ExecuteReader(); List <int> availableHours = new List <int>(); while (dr.Read()) { if (dr["8"].ToString() == "") { availableHours.Add(8); } if (dr["9"].ToString() == "") { availableHours.Add(9); } if (dr["10"].ToString() == "") { availableHours.Add(10); } if (dr["11"].ToString() == "") { availableHours.Add(11); } if (dr["12"].ToString() == "") { availableHours.Add(12); } if (dr["13"].ToString() == "") { availableHours.Add(13); } if (dr["14"].ToString() == "") { availableHours.Add(14); } if (dr["15"].ToString() == "") { availableHours.Add(15); } if (dr["16"].ToString() == "") { availableHours.Add(16); } if (dr["17"].ToString() == "") { availableHours.Add(17); } if (dr["18"].ToString() == "") { availableHours.Add(18); } if (dr["19"].ToString() == "") { availableHours.Add(19); } if (dr["20"].ToString() == "") { availableHours.Add(20); } if (dr["21"].ToString() == "") { availableHours.Add(21); } break; } dr.Close(); cn.Close(); //int flag = 0; //List<int> lectHours = new List<int>(); for (int i = 0; i < availableHours.Count() - 1; i++) { if (availableHours[i] == availableHours[i + 1] - 1) { taHours.Add(availableHours[i]); if (taHours.Contains(availableHours[i + 1])) { continue; } else { taHours.Add(availableHours[i + 1]); } //flag++; this.countHours++; } } }