public List <TeacherShowAll> GetTeacherCreditInfo(int teacherId)
{
SqlConnection connection = new SqlConnection(connectionString);
string quary = "select SUM(C.Credit) AS TotalCredit, T.CreditToBeTaken from Teacher AS T FULL OUTER JOIN TeacherAssign AS TA ON TA.TeacherId = T.Id FULL OUTER JOIN Course AS C ON C.Id = TA.CourseId Where T.Id = '" + teacherId + "' Group By T.CreditToBeTaken";
SqlCommand command = new SqlCommand(quary, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <TeacherShowAll> teacherCreditList = new List <TeacherShowAll>();
while (reader.Read())
{
TeacherShowAll teachersCrdit = new TeacherShowAll();
if (reader["TotalCredit"] != DBNull.Value)
{
teachersCrdit.TotalCredit = Convert.ToInt32(reader["TotalCredit"].ToString());
}
teachersCrdit.CreditToBeTaken = Convert.ToInt32(reader["CreditToBeTaken"].ToString());
teacherCreditList.Add(teachersCrdit);
}
reader.Close();
connection.Close();
return(teacherCreditList);
}
public List <TeacherShowAll> GetAllTeacher()
{
SqlConnection connection = new SqlConnection(connectionString);
string quary = "select Teacher.Name,Teacher.Email,Teacher.Address,Teacher.CreditToBeTaken,Teacher.ContactNo,Designation.Name AS Designation,Department.Name AS Department from Teacher Join Designation ON Designation.Id = Teacher.DesignationId Join Department ON Department.Id = Teacher.DepartmentId order by Department.Id";
SqlCommand command = new SqlCommand(quary, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <TeacherShowAll> teacherList = new List <TeacherShowAll>();
while (reader.Read())
{
TeacherShowAll teacherShowAll = new TeacherShowAll();
teacherShowAll.Name = reader["Name"].ToString();
teacherShowAll.Address = reader["Address"].ToString();
teacherShowAll.Email = reader["Email"].ToString();
teacherShowAll.ContactNo = reader["ContactNo"].ToString();
teacherShowAll.DesignationName = reader["Designation"].ToString();
teacherShowAll.DepartmentName = reader["Department"].ToString();
teacherShowAll.CreditToBeTaken = Convert.ToInt32(reader["CreditToBeTaken"].ToString());
teacherList.Add(teacherShowAll);
}
reader.Close();
connection.Close();
return(teacherList);
}
