I'am trying to prepare a scheduling algorithm where i have used 3 DataTables in order to hold lecturer details panel details and hall availability details. I am using for loops to access DataTables. I found that for loop control structure doesn't help me in this situation to give the correct output
Here is my SQL statements for create tables
create table Lecturer_availability(
lecid int,
name varchar(100),
dat1 varchar(100),
time1 varchar(100),
dat2 varchar(100),
time2 varchar(100),
dat3 varchar(100),
time3 varchar(100),
)
create table panel(
groupid int,
e1 int, //examiner 1
e2 int, //examiner 2
sup int, //supervisor
)
create table Location_availability(
hname varchar(100),
dat1 varchar(100),
time1 varchar(100),
dat2 varchar(100),
time2 varchar(100),
dat3 varchar(100),
time3 varchar(100),
)
create table Schedule(
Location varchar(100),
Group_ID varchar(100),
Date varchar(100),
Time_slot varchar(100),
Examiner_1 varchar(100),
Examiner_2 varchar(100),
Supervisor varchar(100),
)
In the c# code i'm creating 3 DataTable and using the for loops i match the lectures avaiable dates and times with the hall available dates and times and insert the verified records to schedule table. panel table holds lecture id's of lecturers
This is my algorithm. Here i consider about date1 and time1 only
string connString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
//Load panel details
var panel = new DataTable();
using (var da = new SqlDataAdapter("SELECT * FROM panel",connString))
{
da.Fill(panel);
}
//Load lecturer avaialability
var lecavaial = new DataTable();
using (var da = new SqlDataAdapter("select * from Lecturer_availability", connString))
{
da.Fill(lecavaial);
}
//Hall Availability
var hallavaial = new DataTable();
using (var da = new SqlDataAdapter("select * from Location_availability", connString))
{
da.Fill(hallavaial);
}
string ex1date1, ex1time1, ex2date1, ex2time1, supdate1, suptime1;
string ex1name, ex2name, sup, hall, groupid, date, time;
for (int j = 0; j < hallavaial.Rows.Count; j++)
{
for (int k = 0; k < panel.Rows.Count; k++)
{
for (int i = 0; i < lecavaial.Rows.Count; i++)
{
if (panel.Rows[k]["e1"].ToString() == lecavaial.Rows[i]["lecid"].ToString())
{
ex1date1 = lecavaial.Rows[i]["dat1"].ToString();
ex1time1 = lecavaial.Rows[i]["time1"].ToString();
if (ex1date1 == hallavaial.Rows[j]["dat1"].ToString())
{
if (ex1time1 == hallavaial.Rows[j]["time1"].ToString())
{
ex1name = lecavaial.Rows[i]["name"].ToString();
Label5.Text = ex1name;
if (panel.Rows[k]["e2"].ToString() == lecavaial.Rows[i]["lecid"].ToString())
{
ex2date1 = lecavaial.Rows[i]["dat1"].ToString();
ex2time1 = lecavaial.Rows[i]["time1"].ToString();
if (ex2date1 == hallavaial.Rows[j]["dat1"].ToString())
{
if (ex2time1 == hallavaial.Rows[j]["time1"].ToString())
{
ex2name = lecavaial.Rows[i]["name"].ToString();
Label6.Text = ex2name;
if (panel.Rows[k]["sup"].ToString() == lecavaial.Rows[i]["lecid"].ToString())
{
supdate1 = lecavaial.Rows[i]["dat1"].ToString();
suptime1 = lecavaial.Rows[i]["time1"].ToString();
if (supdate1 == hallavaial.Rows[j]["dat1"].ToString())
{
if (suptime1 == hallavaial.Rows[j]["time1"].ToString())
{
sup = lecavaial.Rows[i]["name"].ToString();
Label7.Text = sup;
}
}
}
}
}
}
}
}
}
hall = hallavaial.Rows[j]["hname"].ToString();
groupid = panel.Rows[k]["gid"].ToString();
date = lecavaial.Rows[i]["dat1"].ToString();
time = lecavaial.Rows[i]["time1"].ToString();
ex1name = Label5.Text;
ex2name = Label6.Text;
sup = Label7.Text;
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand("INSERT INTO Schedule (Location,Group_ID,Date,Time_slot,Examiner_1,Examiner_2,Supervisor) VALUES (@loc, @gid, @date, @time, @ex1, @ex2, @sup)");
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@loc", hall);
cmd.Parameters.AddWithValue("@gid", groupid);
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@time", time);
cmd.Parameters.AddWithValue("@ex1", ex1name);
cmd.Parameters.AddWithValue("@ex2", ex2name);
cmd.Parameters.AddWithValue("@sup", sup);
connection.Open();
cmd.ExecuteNonQuery();
}
}
}
}
This is how i should have my output
