public List <EnrollStudentInCourse> GetEnrollCourses()
{
CommandObj.CommandText = "SELECT * FROM StudentEnrollInCourse_tbl";
List <EnrollStudentInCourse> enrollStudentInCourses = new List <EnrollStudentInCourse>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
EnrollStudentInCourse enrollStudentInCourse = new EnrollStudentInCourse
{
Id = Convert.ToInt32(reader["Id"].ToString()),
StudentId = Convert.ToInt32(reader["StudentId"].ToString()),
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
EnrollDate = Convert.ToDateTime(reader["EnrollDate"].ToString()),
Status = Convert.ToBoolean(reader["IsStudentActive"].ToString())
};
enrollStudentInCourses.Add(enrollStudentInCourse);
}
reader.Close();
ConnectionObj.Close();
CommandObj.Dispose();
return(enrollStudentInCourses);
}
public int Update(EnrollStudentInCourse enrollStudentInCourse)
{
ConnectionObj.Open();
SqlTransaction sqlTransaction = ConnectionObj.BeginTransaction();
try
{
CommandObj.Transaction = sqlTransaction;
CommandObj.CommandText = "UPDATE t_StudentEnrollInCourse SET IsStudentActive=1 WHERE StudentId='" + enrollStudentInCourse.StudentId + "' AND CourseId='" + enrollStudentInCourse.CourseId + "'";
int updateResult = CommandObj.ExecuteNonQuery();
// int updateResult = UpdateStudentEnrolledCourses(enrollStudentInCourse);
sqlTransaction.Commit();
return(updateResult);
}
catch (Exception exception)
{
sqlTransaction.Rollback();
throw new Exception("Could not save", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public ActionResult EnrollStudentInCourse(EnrollStudentInCourse enrollStudentInCourse)
{
ViewBag.students = aStudentManager.GetAllStudent();
//ViewBag.courses = aCourseManager.GetAllCouses();
ViewBag.message = aEnrollStudentInCourseManager.Save(enrollStudentInCourse);
return(View());
}
public ActionResult Enroll(EnrollStudentInCourse enrollStudent)
{
string message;
message = studentManager.Save(enrollStudent);
ViewBag.Message = message;
IEnumerable <Student> students = studentManager.GetAllStudents();
IEnumerable <Course> courses = courseManager.GetAllCourses();
ViewBag.Students = students;
ViewBag.Courses = courses;
return(View());
}
public int EnrollStudentInACourse(EnrollStudentInCourse enrollStudent)
{
CommandObj.CommandText = "INSERT INTO StudentEnrollInCourse_tbl (StudentId,CourseId,EnrollDate,IsStudentActive) VALUES(@stId,@courseId,@enrollDate,@status)";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@stId", enrollStudent.StudentId);
CommandObj.Parameters.AddWithValue("@courseId", enrollStudent.CourseId);
CommandObj.Parameters.AddWithValue("@enrollDate", enrollStudent.EnrollDate.ToShortDateString());
CommandObj.Parameters.AddWithValue("@status", 1);
ConnectionObj.Open();
int rowAffected = CommandObj.ExecuteNonQuery();
CommandObj.Dispose();
ConnectionObj.Close();
return(rowAffected);
}
public string Save(EnrollStudentInCourse enrollStudentInCourse)
{
if (AlreadyEnrolledInCourse(enrollStudentInCourse.StudentId, enrollStudentInCourse.CourseId))
{
return("Student has already been enrolled the Course.");
}
else
{
if (aEnrollStudentInCourseGateway.Save(enrollStudentInCourse) > 0)
{
return("Course is Enrolled.");
}
return("Course enroll failed!");
}
}
public int Save(EnrollStudentInCourse enrollStudentInCourse)
{
Query = "INSERT INTO EnrollCourse_tb (StudentId,CourseId,EnrollDate) VALUES (@studentId,@courseId,@enrollDate)";
Command = new SqlCommand(Query, Connection);
Command.Parameters.Clear();
Command.Parameters.AddWithValue("studentId", enrollStudentInCourse.StudentId);
Command.Parameters.AddWithValue("courseId", enrollStudentInCourse.CourseId);
Command.Parameters.AddWithValue("enrollDate", enrollStudentInCourse.Date);
Connection.Open();
int rowAffected = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffected);
}
public string Save(EnrollStudentInCourse enrollStudentInCourse)
{
EnrollStudentInCourse enrollStudent =
GetEnrollCourses().ToList().Find(s => (s.StudentId == enrollStudentInCourse.StudentId && s.CourseId == enrollStudentInCourse.CourseId) && (s.Status));
if (enrollStudent == null)
{
if (studentGateway.EnrollStudentInACourse(enrollStudentInCourse) > 0)
{
return("Saved Successfully");
}
return("Failed to save");
}
return("Already Enroll in this course");
}
public int UpdateStudentEnrollInCourseIsActive(EnrollStudentInCourse enrollStudentInCourse)
{
ConnectionObj.Open();
//CommandObj.CommandText = "UPDATE StudentEnrollInCourse_tbl SET IsStudentActive=1 WHERE StudentId='" + enrollStudentInCourse.StudentId + "' AND CourseId='" + enrollStudentInCourse.CourseId + "'";
CommandObj.CommandText = "UPDATE StudentEnrollInCourse_tbl SET IsStudentActive=1 WHERE StudentId=@StudentId AND CourseId=@CourseId";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("StudentId", enrollStudentInCourse.StudentId);
CommandObj.Parameters.AddWithValue("CourseId", enrollStudentInCourse.CourseId);
int updateResult = CommandObj.ExecuteNonQuery();
// int updateResult = UpdateStudentEnrolledCourses(enrollStudentInCourse);
CommandObj.Dispose();
ConnectionObj.Close();
return(updateResult);
}
public string Save(EnrollStudentInCourse enrollStudentInCourse)
{
EnrollStudentInCourse enrollStudent =
GetEnrollCourses.ToList()
.Find(
st =>
(st.StudentId == enrollStudentInCourse.StudentId &&
st.CourseId == enrollStudentInCourse.CourseId) && (st.Status));
if (enrollStudent == null)
{
if (studentGateway.Insert(enrollStudentInCourse) > 0)
{
return("Saved Sucessfully!");
}
return("Failed to save");
}
return("This course already taken by the student");
}
public ActionResult Enroll(EnrollStudentInCourse enrollStudent)
{
string message;
try
{
message = studentManager.Save(enrollStudent);
ViewBag.Message = message;
IEnumerable <Student> students = studentManager.GetAll;
IEnumerable <Course> courses = courseManager.GetAll;
ViewBag.Students = students;
ViewBag.Courses = courses;
return(View());
}
catch (Exception exception)
{
message = exception.InnerException.Message;
ViewBag.Message = message;
return(View());
}
}
public List <EnrollStudentInCourse> GetEnrollStudentCourses()
{
Query = "SELECT ec.Id,ec.CourseId,ec.StudentId,c.Name FROM EnrollCourse_tb AS ec LEFT JOIN Course_tb AS c on ec.CourseId = c.Id";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
List <EnrollStudentInCourse> enrollStudentCourses = new List <EnrollStudentInCourse>();
while (Reader.Read())
{
EnrollStudentInCourse enrollStudentCourse = new EnrollStudentInCourse
{
Id = (int)Reader["Id"],
CourseName = Reader["Name"].ToString(),
CourseId = (int)Reader["CourseId"],
StudentId = (int)Reader["StudentId"],
};
enrollStudentCourses.Add(enrollStudentCourse);
}
Reader.Close();
Connection.Close();
return(enrollStudentCourses);
}
public int Insert(EnrollStudentInCourse enrollStudent)
{
try
{
CommandObj.CommandText = "INSERT INTO t_StudentEnrollInCourse VALUES(@stId,@courseId,@enrollDate,@status)";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@stId", enrollStudent.StudentId);
CommandObj.Parameters.AddWithValue("@courseId", enrollStudent.CourseId);
CommandObj.Parameters.AddWithValue("@enrollDate", enrollStudent.EnrollDate.ToShortDateString());
CommandObj.Parameters.AddWithValue("@status", 1);
ConnectionObj.Open();
int rowAffected = CommandObj.ExecuteNonQuery();
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Could not save", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
