public TimeTableData extraclass(String course, String Section, int day, String instructor)
{
List <TimeTableData> TTD = FetchTimetable();
int checker = 0;
for (int ts = 1; ts < 9; ts++)
{
checker = 0;
for (int i = 0; i < TTD.Count; i++)
{
if (TTD[i].day == 1 && TTD[i].course.Equals(course) && TTD[i].section.Equals(Section) && TTD[i].timeslot == ts && TTD[i].empname.Equals(instructor))
{
checker = 1;
break;
}
}
if (checker == 0)
{
TimeTableData td = new TimeTableData();
td.course = course;
td.day = day;
td.empname = instructor;
td.timeslot = ts;
td.section = Section;
td.room = "" + (roomempty(day, ts) + 1);
string connectionString = ConfigurationManager.AppSettings["SqlDBConn"].ToString();
SqlConnection con = new SqlConnection(connectionString);
con.Open();
String batcher = "Batch16";
for (int i = 0; i < TTD.Count; i++)
{
if (TTD[i].course.Equals(td.course))
{
batcher = TTD[i].batch;
break;
}
}
int rooms = roomempty(day, ts) + 1;
int batchid = getBatchID(batcher);
SqlCommand cmd = new SqlCommand("insert into Timetable(empID,dayID,courseID,sectionID,batchID,roomID,timeslotID) values (" + getinstructorID(td.empname) + "," + td.day + "" +
"," + getCourseID(td.course) + "," + getSectionID(td.section, batchid) + "," + batchid + "," + rooms + "," + td.timeslot + ")", con);
int asd = cmd.ExecuteNonQuery();
con.Close();
td.batch = batcher;
return(td);
}
}
return(null);
}
public List <TimeTableData> FetchTimetable()
{
//populate this list will the items needed from the DB
List <TimeTableData> TTM = new List <TimeTableData>();
string queryString = "select Employee.EmpName,Course.CourseName,Section.SectionName,Batch.BatchName, daysofweek.dayvalue,Rooms.Roomname,Timeslot.Slot " +
"from Employee,Course,Batch,daysofweek,Section,Rooms,Timeslot,Timetable " +
"where Timetable.empID = Employee.EmpID and Timetable.courseID = Course.CourseID and " +
"Section.SectionID = Timetable.sectionID and Batch.BatchID = Timetable.batchID and " +
"Timetable.batchID = Section.BatchID and Rooms.RoomID = Timetable.roomID and " +
"Timetable.timeslotID = Timeslot.TimeslotID and Timetable.dayID = daysofweek.dayID;";
string connectionString = ConfigurationManager.AppSettings["SqlDBConn"].ToString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
int i = 0;
while (reader.Read())
{
//i++;
TimeTableData temp = new TimeTableData();
temp.course = reader.GetString(reader.GetOrdinal("CourseName"));
temp.empname = reader.GetString(reader.GetOrdinal("EmpName"));
temp.section = reader.GetString(reader.GetOrdinal("SectionName"));
temp.batch = reader.GetString(reader.GetOrdinal("BatchName"));
temp.room = reader.GetString(reader.GetOrdinal("Roomname"));
temp.day = reader.GetInt32(reader.GetOrdinal("dayvalue"));
temp.timeslot = reader.GetInt32(reader.GetOrdinal("Slot"));
TTM.Add(temp);
}
}
finally
{
reader.Close();
}
}
return(TTM);
}
public TimeTableData FetchClassroom(String course, String Section, int day)
{
TimeTableData temp = new TimeTableData();
string queryString = "select Employee.EmpName,Course.CourseName,Section.SectionName,Batch.BatchName, daysofweek.dayvalue,Rooms.Roomname,Timeslot.Slot " +
"from Employee,Course,Batch,daysofweek,Section,Rooms,Timeslot,Timetable " +
"where Timetable.empID = Employee.EmpID and Timetable.courseID = Course.CourseID and " +
"Section.SectionID = Timetable.sectionID and Batch.BatchID = Timetable.batchID and " +
"Timetable.batchID = Section.BatchID and Rooms.RoomID = Timetable.roomID and " +
"Timetable.timeslotID = Timeslot.TimeslotID and Timetable.dayID = daysofweek.dayID" +
" and daysofweek.dayvalue = " + day + " and SectionName like '" + Section + "' and CourseName like '" + course + "' ;";
string connectionString = ConfigurationManager.AppSettings["SqlDBConn"].ToString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
temp.course = reader.GetString(reader.GetOrdinal("CourseName"));
temp.empname = reader.GetString(reader.GetOrdinal("EmpName"));
temp.section = reader.GetString(reader.GetOrdinal("SectionName"));
temp.batch = reader.GetString(reader.GetOrdinal("BatchName"));
temp.room = reader.GetString(reader.GetOrdinal("Roomname"));
temp.day = reader.GetInt32(reader.GetOrdinal("dayvalue"));
temp.timeslot = reader.GetInt32(reader.GetOrdinal("Slot"));
}
}
finally
{
reader.Close();
}
}
return(temp);
}
