//insert user to the table sched1_lecturer_A - lecturer that doesn't exsit in the table
public bool insertToSchecd1(Lecturer lect)
{
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 sched1_lecturer_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 [sched1_lecturer_A] (Num,ID, lecturer,day) values(@Num , @ID, @lecturer , @Day );";
cmd = new SqlCommand(q, cn);
cmd.Parameters.AddWithValue("@Num", nextIndex);
cmd.Parameters.AddWithValue("@ID", lect.getID());
cmd.Parameters.AddWithValue("@lecturer", lect.mergetoOne(lect.getFirstName(), lect.getLastName()));
cmd.Parameters.AddWithValue("@Day", this.course.getDayL());
//cmd.CommandText = "SELECT SCOPE_IDENTITY()";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//dr = cmd.ExecuteReader();
cn.Close();
dr.Close();
return(true);
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
return(false);
}
// to check the available hours of the lecturer
public void findFreeHours(Lecturer lec)
{
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 sched1_lecturer_A WHERE lecturer ='" + lec.mergetoOne(lec.getFirstName(), lec.getLastName()) + "' and day = '" + this.comboBox3.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)
{
lectHours.Add(availableHours[i]);
if (lectHours.Contains(availableHours[i + 1]))
{
continue;
}
else
{
lectHours.Add(availableHours[i + 1]);
}
//flag++;
this.countHours++;
}
}
}