What's the proper loop control structure to give the correct output?

58 views Asked by At

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

Schedule table after comparisons

0

There are 0 answers