public int Save_Teacher(Teacher teacher)
{
connection.ConnectionString = connectionString;
string sqlQuery = "INSERT INTO teachers_tbl VALUES(@Name,@Address,@Email,@Phone,@Designation,@DepartmentId,@TakenCredit)";
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Parameters.Clear();
command.Parameters.Add("Name", SqlDbType.VarChar);
command.Parameters["Name"].Value = teacher.Name;
command.Parameters.Add("Address", SqlDbType.VarChar);
command.Parameters["Address"].Value = teacher.Address;
command.Parameters.Add("Email", SqlDbType.VarChar);
command.Parameters["Email"].Value = teacher.Email;
command.Parameters.Add("Phone", SqlDbType.VarChar);
command.Parameters["Phone"].Value = teacher.Phone;
command.Parameters.Add("Designation", SqlDbType.VarChar);
command.Parameters["Designation"].Value = teacher.Designation;
command.Parameters.Add("DepartmentId", SqlDbType.Int);
command.Parameters["DepartmentId"].Value = teacher.DepartmentId;
command.Parameters.Add("TakenCredit", SqlDbType.Decimal);
command.Parameters["TakenCredit"].Value = teacher.TakenCredit;
connection.Open();
int isInserted = command.ExecuteNonQuery();
connection.Close();
return isInserted;
}
public List<Teacher> GetTeacherByDepartment(int departmentId)
{
connection.ConnectionString = connectionString;
string sqlQuery = "SELECT * FROM teachers_tbl WHERE department_id='" + departmentId + "'";
SqlCommand command=new SqlCommand(sqlQuery,connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List<Teacher> teachersList=new List<Teacher>();
while (reader.Read())
{
Teacher teacher = new Teacher
{
Id = Convert.ToInt32(reader["id"]),
Name = reader["name"].ToString()
};
teachersList.Add(teacher);
}
return teachersList;
}
public string Save_Teacher(Teacher teacher)
{
if (!teacherGatway.IsExistsEmail(teacher.Email))
{
if (teacherGatway.Save_Teacher(teacher) > 0)
{
return "New Teacher Are Joined Name : " + teacher.Name;
}
else
{
return "Teacher are Failed to Save !!!";
}
}
else
{
return "Teacher's Email Address Is Duplicate";
}
}
public object CheckTeachersCredit(int teachersId)
{
connection.ConnectionString = connectionString;
string sqlQueryTaknCredit = "SELECT credit FROM assigned_course_tbl where teachers_id='"+teachersId+"' AND status=1";
SqlCommand command = new SqlCommand(sqlQueryTaknCredit, connection);
connection.Open();
SqlDataReader creditReader = command.ExecuteReader();
decimal takenCredit = 0;
while (creditReader.Read())
{
decimal credit = Convert.ToDecimal(creditReader["credit"]);
takenCredit += credit;
}
creditReader.Close();
connection.Close();
string teachersTotalCredit = "SELECT taken_credit FROM teachers_tbl where id='" + teachersId + "'";
SqlCommand command2 = new SqlCommand(teachersTotalCredit, connection);
connection.Open();
SqlDataReader creditReaderforTeacher = command2.ExecuteReader();
decimal takenCreditTotal = 0;
while (creditReaderforTeacher.Read())
{
takenCreditTotal = Convert.ToDecimal(creditReaderforTeacher["taken_credit"]);
}
creditReaderforTeacher.Close();
connection.Close();
decimal remainingTeachersCredit = takenCreditTotal - takenCredit;
Teacher teachers = new Teacher
{
TakenCredit = takenCreditTotal,
RemainingCredit = remainingTeachersCredit
};
return teachers;
}
public ActionResult Save_Teacher(Teacher teachers)
{
ViewBag.Message = teacherManager.Save_Teacher(teachers);
List<Department> departmentsList = departmentManager.GetDepartmentsList();
return View(departmentsList);
}