public List <StudentGom> GetallStudent()
{
Query = "SELECT * FROM Student";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
List <StudentGom> students = new List <StudentGom>();
while (Reader.Read())
{
StudentGom astudent = new StudentGom();
astudent.StudentId = Convert.ToInt32(Reader["StudentId"]);
astudent.RegNo = Reader["RegNo"].ToString();
astudent.Name = Reader["StudentName"].ToString();
astudent.Email = Reader["StudentEmail"].ToString();
astudent.DeptId = Convert.ToInt32(Reader["DepartmentId"]);
//studentInfo.StudentDeptName = Reader["DepartmentName"].ToString();
//studentInfo.StudentDeptCode = Reader["DepartmentCode"].ToString();
//studentInfo.CourseCode = Reader["CourseCode"].ToString();
//studentInfo.CourseName = Reader["CourseName"].ToString();
//studentInfo.Grade = Reader["Grade"].ToString();
//student.StudentdeptName = Reader["DepartmentId"];
students.Add(astudent);
}
Connection.Close();
Reader.Close();
return(students);
}
public int Save(StudentGom student)
{
Connection = new SqlConnection(connectionString);
Query = "INSERT INTO Student(RegNo,StudentName,StudentEmail,StudentContactNo,RegDate,StudentAddress,DepartmentId) VALUES(@RegNo,@StudentName,@StudentEmail,@StudentContactNo,@RegDate,@StudentAddress,@DepartmentId)";
Command = new SqlCommand(Query, Connection);
Command.Parameters.Clear();
Command.Parameters.Add("RegNo", SqlDbType.VarChar);
Command.Parameters["RegNo"].Value = student.RegNo;
Command.Parameters.Add("StudentName", SqlDbType.VarChar);
Command.Parameters["StudentName"].Value = student.Name;
Command.Parameters.Add("StudentEmail", SqlDbType.VarChar);
Command.Parameters["StudentEmail"].Value = student.Email;
Command.Parameters.Add("StudentContactNo", SqlDbType.VarChar);
Command.Parameters["StudentContactNo"].Value = student.ContactNo;
Command.Parameters.Add("RegDate", SqlDbType.Date);
Command.Parameters["RegDate"].Value = student.RegDate;
Command.Parameters.Add("StudentAddress", SqlDbType.VarChar);
Command.Parameters["StudentAddress"].Value = student.Address;
Command.Parameters.Add("DepartmentId", SqlDbType.Int);
Command.Parameters["DepartmentId"].Value = student.DeptId;
Connection.Open();
int rowAffected = 0;
try
{
rowAffected = Command.ExecuteNonQuery();
}
catch
{
rowAffected = -999;
}
Connection.Close();
return(rowAffected);
}
public ActionResult ViewRegisteredStudent(StudentGom student)
{
//ShowDept();
string msg = "";
SaveStudentManagerGOM save = new SaveStudentManagerGOM();
StudentGom registeredStudent = save.Save(student);
ViewBag.registeredStudent = registeredStudent;
//msg = save.Save(student);
//ViewBag.msg = msg;
return(View());
}
public StudentGom SeachStudentByEmail(string email)
{
Query = "SELECT * FROM Student AS s,Department as d WHERE s.StudentEmail='" + email + "' AND s.DepartmentId=d.DepartmentId";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
StudentGom registeredStudent = new StudentGom();
while (Reader.Read())
{
//studentInfo.StudentId = Convert.ToInt32(Reader["StudentId"]);
registeredStudent.RegNo = Reader["RegNo"].ToString();
registeredStudent.Name = Reader["StudentName"].ToString();
registeredStudent.Email = Reader["StudentEmail"].ToString();
//studentInfo.StudentDeptId = Convert.ToInt32(Reader["DepartmentId"]);
registeredStudent.DeptName = Reader["DepartmentName"].ToString();
registeredStudent.DeptCode = Reader["DepartmentCode"].ToString();
registeredStudent.ContactNo = Reader["StudentContactNo"].ToString();
registeredStudent.Address = Reader["StudentAddress"].ToString();
registeredStudent.RegDate = Convert.ToDateTime(Reader["RegDate"]);
//studentInfo.CourseName = Reader["CourseName"].ToString();
//studentInfo.Grade = Reader["Grade"].ToString();
//student.StudentdeptName = Reader["DepartmentId"];
//StudentListAndResult.Add(studentInfo);
}
Connection.Close();
Reader.Close();
DateTime d = DateTime.Now;
registeredStudent.PresentDate = d;
return(registeredStudent);
}
public int CountStudentId(string code)
{
Query = "SELECT COUNT(RegNo) as Registration FROM Student WHERE RegNo LIKE '%" + code + "%'";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
StudentGom s = new StudentGom();
while (Reader.Read())
{
s.StudentId = Convert.ToInt32(Reader["Registration"].ToString());
}
Connection.Close();
Reader.Close();
return(s.StudentId);
}
public string CheckUniqueEmail(string email)
{
Query = "SELECT * FROM Student WHERE StudentEmail=@StudentEmail";
Command = new SqlCommand(Query, Connection);
Command.Parameters.Clear();
Command.Parameters.Add("StudentEmail", SqlDbType.VarChar);
Command.Parameters["StudentEmail"].Value = email;
Connection.Open();
StudentGom student = new StudentGom();
try
{
Reader = Command.ExecuteReader();
while (Reader.Read())
{
student.Email = Reader["StudentEmail"].ToString();
}
Reader.Close();
}
catch
{
student.Email = null;
}
Connection.Close();
return(student.Email);
}
public StudentGom Save(StudentGom student)
{
//DateTime d=DateTime.Now;
//string date = d.Year.ToString();
string date = student.RegDate.Year.ToString();
ViewDeptGateWayGOM dept = new ViewDeptGateWayGOM();
ViewStudentManagerGOM viewStudent = new ViewStudentManagerGOM();
string code = dept.ShowDeptCode(student.DeptId.ToString()) + "-" + date;
int count = viewStudent.CountStudentId(code);
string id = "";
count = count + 1;
if (count > 0 && count < 10)
{
id = "00" + count.ToString();
}
if (count > 9 && count < 100)
{
id = "0" + count.ToString();
}
student.RegNo = code + "-" + id;
string msg = "Data Insertion Failed";
StudentUniqueEmailGatewayGOM email = new StudentUniqueEmailGatewayGOM();
string ourEmail = email.CheckUniqueEmail(student.Email);
StudentGom registeredStudent = new StudentGom();
if (ourEmail != null)
{
registeredStudent.RegNo = "Please Enter Unique Email";
return(registeredStudent);
}
else
{
SaveStudentGatewayGOM saveStudent = new SaveStudentGatewayGOM();
int rowAffected = saveStudent.Save(student);
if (rowAffected > 0)
{
ViewStudentGatewayGOM searchByEmail = new ViewStudentGatewayGOM();
//msg = "Data Successfull Inserted";
registeredStudent = searchByEmail.SeachStudentByEmail(student.Email);
}
}
return(registeredStudent);
}
